package yongxin.controller.sale;

import java.io.OutputStream;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.jeecgframework.web.system.service.SystemService;

import org.apache.log4j.Logger;
import org.jeecgframework.core.common.controller.BaseController;
import org.jeecgframework.core.common.model.json.AjaxJson;
import org.jeecgframework.core.common.model.json.DataGrid;
import org.jeecgframework.core.constant.Globals;
import org.jeecgframework.core.util.MyBeanUtils;
import org.jeecgframework.core.util.StringUtil;
import org.jeecgframework.tag.core.easyui.TagUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.servlet.ModelAndView;

import yongxin.dao.sale.SaleMiniDao;
import yongxin.utils.ExcelUtils;

/**
 * @Title: Controller
 * @Description: 销售有关
 * @author fancq
 * @date 2013-12-23 21:18:59
 * @version V1.0
 * 
 */
@Controller
@RequestMapping("/saleMinidaoController")
public class SaleMinidaoController extends BaseController {
	/**
	 * Logger for this class
	 */
	private static final Logger logger = Logger.getLogger(SaleMinidaoController.class);

	@Autowired
	private SaleMiniDao saleMiniDao;
	@Autowired
	private SystemService systemService;
	private String message;

	public String getMessage() {
		return message;
	}

	public void setMessage(String message) {
		this.message = message;
	}

	/**
	 * 销售出库汇总（依客户） 页面跳转
	 * 
	 * @return
	 */
	@RequestMapping(params = "saleOutSumListByCu")
	public ModelAndView saleOutSumListByCu(HttpServletRequest request) {
		return new ModelAndView("yongxin/kingdeeReport/saleOutSumByCuList");
	}

	/**
	 * 销售出库汇总（依产品）页面跳转
	 * 
	 * @return
	 */
	@RequestMapping(params = "saleOutSumListByIt")
	public ModelAndView saleOutSumListByIt(HttpServletRequest request) {
		return new ModelAndView("yongxin/kingdeeReport/saleOutSumByItList");
	}
    

	/**
	 * 合同执行情况表 页面跳转
	 * 
	 * @return
	 */
	@RequestMapping(params = "sale")
	public ModelAndView sale(HttpServletRequest request) {
		return new ModelAndView("yongxin/kingdeeReport/contractExecuteList");
	}
    
	/**
	 * 销售及欠款分析
	 * 
	 * @return
	 */
	@RequestMapping(params = "sale2")
	public ModelAndView sale2(HttpServletRequest request) {
		return new ModelAndView("yongxin/kingdeeReport/saleDebtAnalysisList");
	}

	/**
	 * 销售汇总  （依产品）
	 * 
	 * @return
	 */
	@RequestMapping(params = "sale3")
	public ModelAndView sale3(HttpServletRequest request) {
		return new ModelAndView("yongxin/kingdeeReport/saleTotalByItList");
	}
	

	
	/**
	 * 销售开票月报表
	 * 
	 * @return
	 */
	@RequestMapping(params = "sale4")
	public ModelAndView sale4(HttpServletRequest request) {
		return new ModelAndView("yongxin/kingdeeReport/icSaleList");
	}
	
    
	/**
	 * 采购合同执行情况
	 * 
	 * @return
	 */
	@RequestMapping(params = "cghtzx")
	public ModelAndView cghtzx(HttpServletRequest request) {
		return new ModelAndView("yongxin/kingdeeReport/cghtzxList");
	}
	/**
	 * 采购价格分析
	 * 
	 * @return
	 */
	@RequestMapping(params = "cgjgfx")
	public ModelAndView cgjgfx(HttpServletRequest request) {
		return new ModelAndView("yongxin/kingdeeReport/cgjgfxList");
	}
	
	
	/**
	 * easyui AJAX请求数据 销售出库汇总 依客户
	 * 
	 * @param request
	 * @param response
	 * @param dataGrid
	 * @param user
	 */

	@RequestMapping(params = "cu_datagrid")
	public void cu_datagrid(HttpServletRequest request, HttpServletResponse response, DataGrid dataGrid) {

		String FDate_begin = request.getParameter("FDate_begin");
		String FDate_end = request.getParameter("FDate_end");
		String FProp01 = request.getParameter("FProp01");
		String FNumber = request.getParameter("FNumber");
		String FName = request.getParameter("FName");
		String FModel = request.getParameter("FModel");

		String sql = "	SET NOCOUNT ON                                                                                                       ";
		sql += "	 Create Table #DATA2 (      FIndex int IDENTITY,                                                                             ";
		sql += "		 FItemID int null,                                                                                                       ";
		sql += "		 FCustID int null,                                                                                                       ";
		sql += "		 FOutQty float null,                                                                                                     ";
		sql += "		 FOutAmount money null,                                                                                                  ";
		sql += "		 FConsignAmount money null,                                                                                              ";
		sql += "		 FConsignPrice  Money Null,                                                                                              ";
		sql += "		 FCommitQty float null,                                                                                                  ";
		sql += "		 FCommitAmount money null)                                                                                               ";
		sql += "	Insert Into #DATA2 (FItemID,FCustID,FOutQty,FOutAmount,FConsignAmount,FConsignPrice)                                         ";
		sql += "	Select v2.FItemID, v1.FSupplyID, v2.FQty,v2.FAmount,v2.FConsignAmount,v2.FConsignPrice*1000                                       ";
		sql += "	From RemoteServer.AIS2009.dbo.ICStockBill v1                                                                                 ";
		sql += "	Join RemoteServer.AIS2009.dbo.ICStockBillEntry v2 On v1.FInterID = v2.FInterID                                               ";
		sql += "	Join RemoteServer.AIS2009.dbo.t_ICItem t1 On v2.FItemID = t1.FItemID                                                         ";
		sql += "	Left Join RemoteServer.AIS2009.dbo.t_Organization t2 On v1.FSupplyID=t2.FItemID                                              ";

		sql += "	 Where v1.FTranType =21                                                                                                      ";
		sql += "	  AND v1.FStatus>0 And v1.FCancelLation=0  AND v1.FROB=1                                                                     ";

		if (StringUtil.isNotEmpty(FDate_begin) && StringUtil.isNotEmpty(FDate_end)) {
			sql += " AND v1.Fdate between " + "'" + FDate_begin + "'" + " and " + "'" + FDate_end + "'";
		}
		if (StringUtil.isNotEmpty(FProp01)) {
			sql += " and t2.FName like " + "'%" + FProp01 + "%'";
		}
		if (StringUtil.isNotEmpty(FNumber)) {
			sql += " and t1.FNumber like " + "'%" + FNumber + "%'";
		}
		if (StringUtil.isNotEmpty(FName)) {
			sql += " and t1.FName like " + "'%" + FName + "%'";
		}
		if (StringUtil.isNotEmpty(FModel)) {
			sql += " and t1.FModel = " + "'" + FModel + "'";
		}

		sql += "	 Insert Into #DATA2 (FItemID,FCustID,FCommitQty,FCommitAmount)                                                               ";
		sql += "	 Select v2.FItemID,v1.FCustID,v2.FQty, Round(case v1.FTranType when 80 then v2.FAmount * isnull(v1.FExchangeRate,1)          ";
		sql += "	 else  (v2.FAmount - v2.FTaxAmount)* isnull(v1.FExchangeRate,1)  end,2)                                                      ";
		sql += "	 FROM  RemoteServer.AIS2009.dbo.ICSale v1                                                                                    ";
		sql += "	Join RemoteServer.AIS2009.dbo.ICSaleEntry v2 On v1.FInterID = v2.FInterID                                                    ";
		sql += "	Join RemoteServer.AIS2009.dbo.t_ICItem t1 On v2.FItemID = t1.FItemID                                                                                  ";
		sql += "	Left Join RemoteServer.AIS2009.dbo.t_Organization t2 On v1.FCustID = t2.FItemID                                                                       ";

		sql += "	 Where v1.FTranType in (80,86)                                                                                              ";
		sql += "	  AND v1.FStatus>0 And v1.FCancelLation=0  AND v1.FROB=1                                                                    ";

		if (StringUtil.isNotEmpty(FDate_begin) && StringUtil.isNotEmpty(FDate_end)) {
			sql += " AND v1.Fdate between " + "'" + FDate_begin + "'" + " and " + "'" + FDate_end + "'";
		}
		if (StringUtil.isNotEmpty(FProp01)) {
			sql += " AND t2.FName like " + "'%" + FProp01 + "%'";
		}
		if (StringUtil.isNotEmpty(FNumber)) {
			sql += " AND t1.FNumber like " + "'%" + FNumber + "%'";
		}
		if (StringUtil.isNotEmpty(FName)) {
			sql += " AND t1.FName like " + "'%" + FName + "%'";
		}
		if (StringUtil.isNotEmpty(FModel)) {
			sql += " AND t1.FModel = " + "'" + FModel + "'";
		}

		sql += "	CREATE TABLE #DATA(                                                                                                         ";
		sql += "	  FProp01 varchar(355) null,                                                                                                ";
		sql += "		   FNumber  varchar(355) null,                                                                                          ";
		sql += "		 FShortNumber  varchar(355) null,                                                                                       ";
		sql += "		 FName  varchar(355) null,                                                                                              ";
		sql += "		 FModel  varchar(355) null,                                                                                             ";
		sql += "		 FUnitName  varchar(355) null,                                                                                          ";
		sql += "		 FQtyDecimal smallint null,                                                                                             ";
		sql += "		 FPriceDecimal smallint null,                                                                                           ";
		sql += "		 FOutQty Decimal(28,10) null,                                                                                           ";
		sql += "		 FOutAmount Decimal(32,10) null,                                                                                        ";
		sql += "		 FConsignAmount Decimal(32,10) NUll,                                                                                    ";
		sql += "		 FConsignPrice  Decimal(32,10) Null,                                                                                    ";
		sql += "		 FCommitQty Decimal(28,10) null,                                                                                        ";
		sql += "		 FCommitAmount Decimal(32,10) null,                                                                                     ";
		sql += "		 FNotCommitAmount Decimal(32,10) null,                                                                                  ";
		sql += "		 FNotCommitQty Decimal(28,10) null,                                                                                     ";
		sql += "		 FCUUnitName  varchar(355) null,                                                                                        ";
		sql += "		 FCUUnitOutQty Decimal(28,10) null,                                                                                     ";
		sql += "		 FCUUnitCommitQty Decimal(28,10) null,                                                                                  ";
		sql += "		 FCUUnitNotCommitQty Decimal(28,10) null,                                                                               ";
		sql += "		 FSumSort smallint not null Default(0),                                                                                 ";
		sql += "		 FID int IDENTITY )                                                                                                     ";

		sql += "	 INSERT INTO #DATA  SELECT CASE  WHEN GROUPING(t2.FName)=1 THEN '合计'                                                      ";
		sql += "	 WHEN GROUPING(t1.FNumber)=1 THEN  CONVERT(varchar(355),t2.FName)+'(小计)'                                                  ";
		sql += "	 ELSE CONVERT(varchar(355),t2.FName) END,                                                                                   ";
		sql += "	 t1.FNumber,'','','','',6,4,ROUND(SUM(ISNULL(V1.FOUTQTY,0)),6),sum(isnull(v1.FOutAmount,0)),                                ";
		sql += "	 sum(Isnull(v1.FConsignAmount,0)), CASE WHEN ROUND(SUM(ISNULL(V1.FOUTQTY,0)),6)=0 THEN 0                                    ";
		sql += "	 ELSE sum(isnull(v1.FConsignAmount,0)) / ROUND(SUM(ISNULL(V1.FOUTQTY,0)),6) END,                                            ";
		sql += "	 sum(isnull(v1.FCommitQty,0)),sum(isnull(v1.FCommitAmount,0)),0,0,'',                                                          ";

		sql += "	 SUM(ISNULL(v1.FOutQty,0)/(Case When ISNULL(t3.FCoefficient,0)=0 Then 1 Else t3.FCoefficient End )),                        ";
		sql += "	 SUM(ISNULL(v1.FCommitQty,0)/(Case When ISNULL(t3.FCoefficient,0)=0 Then 1 Else t3.FCoefficient End )),                     ";
		sql += "	 0,  CASE WHEN GROUPING(t2.FName)=1 THEN 101                                                                                ";
		sql += "	 WHEN  GROUPING(t1.FNumber)=1 THEN 102 ELSE 0 END  From #DATA2 v1                                                           ";
		sql += "	 Inner Join RemoteServer.AIS2009.dbo.t_ICItem t1 On v1.FItemID=t1.FItemID                                                                            ";
		sql += "	 Inner Join RemoteServer.AIS2009.dbo.t_Organization t2 On v1.FCustID=t2.FItemID                                                                      ";
		sql += "	 Inner Join RemoteServer.AIS2009.dbo.T_MeasureUnit t3 On t1.FSaleUnitID = t3.FMeasureUnitID                                                          ";
		sql += "	 WHERE 1=1                                                                                                                  ";

		sql += "	 GROUP BY t2.FName,t1.FNumber WITH ROLLUP                                                                                   ";

		sql += "	 Update #DATA set  FNotCommitAmount = isnull(FOutAmount,0) - isnull(FCommitAmount,0),                                       ";
		sql += "	 FNotCommitQty = FOutQty-(case when FCommitQty is null then 0 else FCommitQty end )                                         ";
		sql += "	 Update #DATA set  FCUUnitNotCommitQty = FCUUnitOutQty-(case when FCUUnitCommitQty is null then 0 else FCUUnitCommitQty end ) ";

		sql += "	Drop Table #DATA2 ";

		sql += " Update t1 Set t1.FName=t2.FName,t1.FShortNumber=t2.FShortNumber,t1.FModel=t2.FModel, t1.FCUUnitName = t4.FName,t1.FUnitName=t3.FName,";
		sql += "	t1.FQtyDecimal=t2.FQtyDecimal,t1.FPriceDecimal=t2.FPriceDecimal ";
		sql += "	From #Data t1,RemoteServer.AIS2009.dbo.t_ICItem t2,RemoteServer.AIS2009.dbo.t_MeasureUnit t3,RemoteServer.AIS2009.dbo.t_MeasureUnit t4 ";
		sql += "	Where t1.FNumber=t2.FNumber  And t2.FUnitGroupID=t3.FUnitGroupID  And t2.FSaleUnitID=t4.FMeasureUnitID  And t3.FStandard=1";

		sql += "	SELECT FProp01,isnull(FNumber,'') AS FNumber,FName,FModel,FCUUnitName,Convert(decimal(18,4),FCUUnitOutQty) AS FCUUnitOutQty,Convert(decimal(18,5),FConsignPrice)*1000 AS FConsignPrice,Convert(decimal(18,5),FConsignAmount) AS FConsignAmount FROM #DATA    ";
		sql += "	Order by FID ";
		sql += "	DROP TABLE #DATA";

		List<Map> list = saleMiniDao.execProcedureByCu(sql);

		// List<JeecgMinidaoEntity> list =
		// jeecgMinidaoService.listAll(jeecgMinidao, dataGrid.getPage(),
		// dataGrid.getRows());

		dataGrid.setTotal(list.size());
		dataGrid.setResults(list);
		TagUtil.datagrid(response, dataGrid);
	}
    
	
	/**
	 * 
	 * 導出 销售出库汇总（依客户）
	 * 
	 * @param request
	 * @param response
	 * @param dataGrid
	 */
	@RequestMapping(params = "export_culist")
	public void export_culist(HttpServletRequest request, HttpServletResponse response, DataGrid dataGrid) {

	
		String FDate_begin = request.getParameter("FDate_begin");
		String FDate_end = request.getParameter("FDate_end");
		String FProp01 = request.getParameter("FProp01");
		String FNumber = request.getParameter("FNumber");
		String FName = request.getParameter("FName");
		String FModel = request.getParameter("FModel");

		String sql = "	SET NOCOUNT ON                                                                                                       ";
		sql += "	 Create Table #DATA2 (      FIndex int IDENTITY,                                                                             ";
		sql += "		 FItemID int null,                                                                                                       ";
		sql += "		 FCustID int null,                                                                                                       ";
		sql += "		 FOutQty float null,                                                                                                     ";
		sql += "		 FOutAmount money null,                                                                                                  ";
		sql += "		 FConsignAmount money null,                                                                                              ";
		sql += "		 FConsignPrice  Money Null,                                                                                              ";
		sql += "		 FCommitQty float null,                                                                                                  ";
		sql += "		 FCommitAmount money null)                                                                                               ";
		sql += "	Insert Into #DATA2 (FItemID,FCustID,FOutQty,FOutAmount,FConsignAmount,FConsignPrice)                                         ";
		sql += "	Select v2.FItemID, v1.FSupplyID, v2.FQty,v2.FAmount,v2.FConsignAmount,v2.FConsignPrice*1000                                       ";
		sql += "	From RemoteServer.AIS2009.dbo.ICStockBill v1                                                                                 ";
		sql += "	Join RemoteServer.AIS2009.dbo.ICStockBillEntry v2 On v1.FInterID = v2.FInterID                                               ";
		sql += "	Join RemoteServer.AIS2009.dbo.t_ICItem t1 On v2.FItemID = t1.FItemID                                                         ";
		sql += "	Left Join RemoteServer.AIS2009.dbo.t_Organization t2 On v1.FSupplyID=t2.FItemID                                              ";

		sql += "	 Where v1.FTranType =21                                                                                                      ";
		sql += "	  AND v1.FStatus>0 And v1.FCancelLation=0  AND v1.FROB=1                                                                     ";

		if (StringUtil.isNotEmpty(FDate_begin) && StringUtil.isNotEmpty(FDate_end)) {
			sql += " AND v1.Fdate between " + "'" + FDate_begin + "'" + " and " + "'" + FDate_end + "'";
		}
		if (StringUtil.isNotEmpty(FProp01)) {
			sql += " and t2.FName like " + "'%" + FProp01 + "%'";
		}
		if (StringUtil.isNotEmpty(FNumber)) {
			sql += " and t1.FNumber like " + "'%" + FNumber + "%'";
		}
		if (StringUtil.isNotEmpty(FName)) {
			sql += " and t1.FName like " + "'%" + FName + "%'";
		}
		if (StringUtil.isNotEmpty(FModel)) {
			sql += " and t1.FModel = " + "'" + FModel + "'";
		}

		sql += "	 Insert Into #DATA2 (FItemID,FCustID,FCommitQty,FCommitAmount)                                                               ";
		sql += "	 Select v2.FItemID,v1.FCustID,v2.FQty, Round(case v1.FTranType when 80 then v2.FAmount * isnull(v1.FExchangeRate,1)          ";
		sql += "	 else  (v2.FAmount - v2.FTaxAmount)* isnull(v1.FExchangeRate,1)  end,2)                                                      ";
		sql += "	 FROM  RemoteServer.AIS2009.dbo.ICSale v1                                                                                    ";
		sql += "	Join RemoteServer.AIS2009.dbo.ICSaleEntry v2 On v1.FInterID = v2.FInterID                                                    ";
		sql += "	Join RemoteServer.AIS2009.dbo.t_ICItem t1 On v2.FItemID = t1.FItemID                                                                                  ";
		sql += "	Left Join RemoteServer.AIS2009.dbo.t_Organization t2 On v1.FCustID = t2.FItemID                                                                       ";

		sql += "	 Where v1.FTranType in (80,86)                                                                                              ";
		sql += "	  AND v1.FStatus>0 And v1.FCancelLation=0  AND v1.FROB=1                                                                    ";

		if (StringUtil.isNotEmpty(FDate_begin) && StringUtil.isNotEmpty(FDate_end)) {
			sql += " AND v1.Fdate between " + "'" + FDate_begin + "'" + " and " + "'" + FDate_end + "'";
		}
		if (StringUtil.isNotEmpty(FProp01)) {
			sql += " AND t2.FName like " + "'%" + FProp01 + "%'";
		}
		if (StringUtil.isNotEmpty(FNumber)) {
			sql += " AND t1.FNumber like " + "'%" + FNumber + "%'";
		}
		if (StringUtil.isNotEmpty(FName)) {
			sql += " AND t1.FName like " + "'%" + FName + "%'";
		}
		if (StringUtil.isNotEmpty(FModel)) {
			sql += " AND t1.FModel = " + "'" + FModel + "'";
		}

		sql += "	CREATE TABLE #DATA(                                                                                                         ";
		sql += "	  FProp01 varchar(355) null,                                                                                                ";
		sql += "		   FNumber  varchar(355) null,                                                                                          ";
		sql += "		 FShortNumber  varchar(355) null,                                                                                       ";
		sql += "		 FName  varchar(355) null,                                                                                              ";
		sql += "		 FModel  varchar(355) null,                                                                                             ";
		sql += "		 FUnitName  varchar(355) null,                                                                                          ";
		sql += "		 FQtyDecimal smallint null,                                                                                             ";
		sql += "		 FPriceDecimal smallint null,                                                                                           ";
		sql += "		 FOutQty Decimal(28,10) null,                                                                                           ";
		sql += "		 FOutAmount Decimal(32,10) null,                                                                                        ";
		sql += "		 FConsignAmount Decimal(32,10) NUll,                                                                                    ";
		sql += "		 FConsignPrice  Decimal(32,10) Null,                                                                                    ";
		sql += "		 FCommitQty Decimal(28,10) null,                                                                                        ";
		sql += "		 FCommitAmount Decimal(32,10) null,                                                                                     ";
		sql += "		 FNotCommitAmount Decimal(32,10) null,                                                                                  ";
		sql += "		 FNotCommitQty Decimal(28,10) null,                                                                                     ";
		sql += "		 FCUUnitName  varchar(355) null,                                                                                        ";
		sql += "		 FCUUnitOutQty Decimal(28,10) null,                                                                                     ";
		sql += "		 FCUUnitCommitQty Decimal(28,10) null,                                                                                  ";
		sql += "		 FCUUnitNotCommitQty Decimal(28,10) null,                                                                               ";
		sql += "		 FSumSort smallint not null Default(0),                                                                                 ";
		sql += "		 FID int IDENTITY )                                                                                                     ";

		sql += "	 INSERT INTO #DATA  SELECT CASE  WHEN GROUPING(t2.FName)=1 THEN '合计'                                                      ";
		sql += "	 WHEN GROUPING(t1.FNumber)=1 THEN  CONVERT(varchar(355),t2.FName)+'(小计)'                                                  ";
		sql += "	 ELSE CONVERT(varchar(355),t2.FName) END,                                                                                   ";
		sql += "	 t1.FNumber,'','','','',6,4,ROUND(SUM(ISNULL(V1.FOUTQTY,0)),6),sum(isnull(v1.FOutAmount,0)),                                ";
		sql += "	 sum(Isnull(v1.FConsignAmount,0)), CASE WHEN ROUND(SUM(ISNULL(V1.FOUTQTY,0)),6)=0 THEN 0                                    ";
		sql += "	 ELSE sum(isnull(v1.FConsignAmount,0)) / ROUND(SUM(ISNULL(V1.FOUTQTY,0)),6) END,                                            ";
		sql += "	 sum(isnull(v1.FCommitQty,0)),sum(isnull(v1.FCommitAmount,0)),0,0,'',                                                          ";

		sql += "	 SUM(ISNULL(v1.FOutQty,0)/(Case When ISNULL(t3.FCoefficient,0)=0 Then 1 Else t3.FCoefficient End )),                        ";
		sql += "	 SUM(ISNULL(v1.FCommitQty,0)/(Case When ISNULL(t3.FCoefficient,0)=0 Then 1 Else t3.FCoefficient End )),                     ";
		sql += "	 0,  CASE WHEN GROUPING(t2.FName)=1 THEN 101                                                                                ";
		sql += "	 WHEN  GROUPING(t1.FNumber)=1 THEN 102 ELSE 0 END  From #DATA2 v1                                                           ";
		sql += "	 Inner Join RemoteServer.AIS2009.dbo.t_ICItem t1 On v1.FItemID=t1.FItemID                                                                            ";
		sql += "	 Inner Join RemoteServer.AIS2009.dbo.t_Organization t2 On v1.FCustID=t2.FItemID                                                                      ";
		sql += "	 Inner Join RemoteServer.AIS2009.dbo.T_MeasureUnit t3 On t1.FSaleUnitID = t3.FMeasureUnitID                                                          ";
		sql += "	 WHERE 1=1                                                                                                                  ";

		sql += "	 GROUP BY t2.FName,t1.FNumber WITH ROLLUP                                                                                   ";

		sql += "	 Update #DATA set  FNotCommitAmount = isnull(FOutAmount,0) - isnull(FCommitAmount,0),                                       ";
		sql += "	 FNotCommitQty = FOutQty-(case when FCommitQty is null then 0 else FCommitQty end )                                         ";
		sql += "	 Update #DATA set  FCUUnitNotCommitQty = FCUUnitOutQty-(case when FCUUnitCommitQty is null then 0 else FCUUnitCommitQty end ) ";

		sql += "	Drop Table #DATA2 ";

		sql += " Update t1 Set t1.FName=t2.FName,t1.FShortNumber=t2.FShortNumber,t1.FModel=t2.FModel, t1.FCUUnitName = t4.FName,t1.FUnitName=t3.FName,";
		sql += "	t1.FQtyDecimal=t2.FQtyDecimal,t1.FPriceDecimal=t2.FPriceDecimal ";
		sql += "	From #Data t1,RemoteServer.AIS2009.dbo.t_ICItem t2,RemoteServer.AIS2009.dbo.t_MeasureUnit t3,RemoteServer.AIS2009.dbo.t_MeasureUnit t4 ";
		sql += "	Where t1.FNumber=t2.FNumber  And t2.FUnitGroupID=t3.FUnitGroupID  And t2.FSaleUnitID=t4.FMeasureUnitID  And t3.FStandard=1";

		sql += "	SELECT FProp01,isnull(FNumber,'') AS FNumber,FName,FModel,FCUUnitName,Convert(decimal(18,4),FCUUnitOutQty) AS FCUUnitOutQty,Convert(decimal(18,5),FConsignPrice)*1000 AS FConsignPrice,Convert(decimal(18,5),FConsignAmount) AS FConsignAmount FROM #DATA    ";
		sql += "	Order by FID ";
		sql += "	DROP TABLE #DATA";                                                             
		
	//	List<Map> list = saleMiniDao.execContract(sql);

	//	List<Map<String, Object>> maplist = null;  saleMiniDao.execProcedureByCu(sql);
    	List<Map> maplist = null;

		try {
		   maplist = saleMiniDao.execProcedureByCu(sql);

			//maplist = systemService.findForJdbc(sql);
			List<String[]> list = new ArrayList<String[]>();

			for (Map<String, Object> map : maplist) {
				String[] arr = new String[map.size()];
				arr[0] = (String) map.get("FProp01");
				arr[1] = (String) map.get("FNumber");
				arr[2] = (String) map.get("FName");
				arr[3] = (String) map.get("FModel");
				arr[4] =  ((String) map.get("FCUUnitName"));
				arr[5] = String.valueOf((BigDecimal) map.get("FCUUnitOutQty"));
				arr[6] = String.valueOf((BigDecimal) map.get("FConsignPrice"));
				arr[7] = String.valueOf((BigDecimal) map.get("FConsignAmount"));
				list.add(arr);

			}

			String[] columnNames = new String[] { "客户名称", "物料代码", "物料名称", "厚度", "单位", "数量", "单价", "金额"};
			String titleName = "销售出库汇总（依客户）.xls";

			OutputStream fOut = response.getOutputStream();
			response.setHeader("Content-Disposition", "attachment; filename="
					+ new String(titleName.getBytes("GB2312"), "ISO8859-1"));

			ExcelUtils.create(request, list, columnNames, titleName, fOut);

		} catch (Exception ex) {

		}

	}
	
	
	
	/**
	 * easyui AJAX请求数据 销售出库汇总 依产品
	 * 
	 * @param request
	 * @param response
	 * @param dataGrid
	 */

	@RequestMapping(params = "it_datagrid")
	public void it_datagrid(HttpServletRequest request, HttpServletResponse response, DataGrid dataGrid) {

		String FDate_begin = request.getParameter("FDate_begin");
		String FDate_end = request.getParameter("FDate_end");
		String FNumber = request.getParameter("FNumber");
		String FName = request.getParameter("FName");
		String FModel = request.getParameter("FModel");

		String sql = "	SET NOCOUNT ON                                                                                                                ";
		sql += "	 Create Table #DATA2 (      FIndex int IDENTITY,                                                                              ";
		sql += "		 FItemID int null,                                                                                                        ";
		sql += "		 FCustID int null,                                                                                                        ";
		sql += "		 FOutQty float null,                                                                                                      ";
		sql += "		 FOutAmount money null,                                                                                                   ";
		sql += "		 FConsignAmount money null,                                                                                               ";
		sql += "		 FConsignPrice  Money Null,                                                                                               ";
		sql += "		 FCommitQty float null,                                                                                                   ";
		sql += "		 FCommitAmount money null)                                                                                                ";
		sql += "	Insert Into #DATA2 (FItemID,FCustID,FOutQty,FOutAmount,FConsignAmount,FConsignPrice)                                          ";
		sql += "	Select v2.FItemID, v1.FSupplyID, v2.FQty,v2.FAmount,v2.FConsignAmount,v2.FConsignPrice                                        ";
		sql += "	From RemoteServer.AIS2009.dbo.ICStockBill v1                                                                                                           ";
		sql += "	Join RemoteServer.AIS2009.dbo.ICStockBillEntry v2 On v1.FInterID = v2.FInterID                                                                         ";
		sql += "	Join RemoteServer.AIS2009.dbo.t_ICItem t1 On v2.FItemID = t1.FItemID                                                                                   ";
		sql += "	Left Join RemoteServer.AIS2009.dbo.t_Organization t2 On v1.FSupplyID=t2.FItemID                                                                        ";
		sql += "                                                                                                                                ";
		sql += "	 Where v1.FTranType =21                                                                                                       ";
		sql += "	  AND v1.FStatus>0 And v1.FCancelLation=0  AND v1.FROB=1                                                                      ";
		// sql +=
		// "	  AND convert(datetime,convert(varchar(30),v1.FDate,111)) >='2014-07-01'                                                      ";
		// sql +=
		// "	 AND convert(datetime,convert(varchar(30),v1.FDate,111)) <='2014-07-30'                                                       ";
		if (StringUtil.isNotEmpty(FDate_begin) && StringUtil.isNotEmpty(FDate_end)) {
			sql += " AND v1.Fdate between " + "'" + FDate_begin + "'" + " and " + "'" + FDate_end + "'";
		}
		if (StringUtil.isNotEmpty(FNumber)) {
			sql += " and t1.FNumber like " + "'%" + FNumber + "%'";
		}
		if (StringUtil.isNotEmpty(FName)) {
			sql += " and t1.FName like " + "'%" + FName + "%'";
		}
		if (StringUtil.isNotEmpty(FModel)) {
			sql += " and t1.FModel = " + "'" + FModel + "'";
		}
		sql += "	 Insert Into #DATA2 (FItemID,FCustID,FCommitQty,FCommitAmount)                                                                ";
		sql += "	 Select v2.FItemID,v1.FCustID,v2.FQty, Round(case v1.FTranType when 80 then v2.FAmount * isnull(v1.FExchangeRate,1) else      ";
		sql += "	 (v2.FAmount - v2.FTaxAmount)* isnull(v1.FExchangeRate,1)  end,2)                                                             ";
		sql += "	 FROM  RemoteServer.AIS2009.dbo.ICSale v1                                                                                                              ";
		sql += "	Join RemoteServer.AIS2009.dbo.ICSaleEntry v2 On v1.FInterID = v2.FInterID                                                                              ";
		sql += "	Join RemoteServer.AIS2009.dbo.t_ICItem t1 On v2.FItemID = t1.FItemID                                                                                   ";
		sql += "	Left Join RemoteServer.AIS2009.dbo.t_Organization t2 On v1.FCustID = t2.FItemID                                                                        ";
		sql += "                                                                                                                                ";
		sql += "	 Where v1.FTranType in (80,86)                                                                                                ";
		sql += "	  AND v1.FStatus>0 And v1.FCancelLation=0  AND v1.FROB=1                                                                      ";
		if (StringUtil.isNotEmpty(FDate_begin) && StringUtil.isNotEmpty(FDate_end)) {
			sql += " AND v1.Fdate between " + "'" + FDate_begin + "'" + " and " + "'" + FDate_end + "'";
		}
		if (StringUtil.isNotEmpty(FNumber)) {
			sql += " and t1.FNumber like " + "'%" + FNumber + "%'";
		}
		if (StringUtil.isNotEmpty(FName)) {
			sql += " and t1.FName like " + "'%" + FName + "%'";
		}
		if (StringUtil.isNotEmpty(FModel)) {
			sql += " and t1.FModel = " + "'" + FModel + "'";
		}
		sql += "	CREATE TABLE #ItemLevel(                                                                                                      ";
		sql += "	 FNumber1 Varchar(355),                                                                                                       ";
		sql += "	 FName1 Varchar(355),                                                                                                         ";
		sql += "	 FItemID int,                                                                                                                 ";
		sql += "	 FNumber VARCHAR(355))                                                                                                        ";
		sql += "	 Insert Into #ItemLevel SELECT  ";
		sql += "	 Case When CharIndex('.',FFullNumber)-1= -1 or FLevel<2 THEN NULL ELSE SUBSTRING(FNumber, 1,CharIndex('.',FFullNumber)-1) END,";
		sql += "	 '',                                                                                                                          ";
		sql += "	 FItemID,FNumber From RemoteServer.AIS2009.dbo.t_Item                                                                                                  ";
		sql += "	 Where FItemClassID=4                                                                                                         ";
		sql += "	 AND FDetail=1  And  FItemID In (SELECT DISTINCT  FItemID  FROM #DATA2 )                                                      ";
		sql += "	 Update t0 SET t0.FName1=t1.FName                                                                                             ";
		sql += "	 From #ItemLevel t0 left join RemoteServer.AIS2009.dbo.t_Item t1 On t0.FNumber1=t1.FNumber  AND t1.FItemClassID=4 AND t1.FDetail=0                     ";
		sql += "                                                                                                                                ";
		sql += "	CREATE TABLE #DATA(                                                                                                           ";
		sql += "	FName1 varchar(355) Null,                                                                                                     ";
		sql += "		   FNumber  varchar(355) null,                                                                                            ";
		sql += "		 FShortNumber  varchar(355) null,                                                                                         ";
		sql += "		 FName  varchar(355) null,                                                                                                ";
		sql += "		 FModel  varchar(355) null,                                                                                               ";
		sql += "		 FUnitName  varchar(355) null,                                                                                            ";
		sql += "		 FQtyDecimal smallint null,                                                                                               ";
		sql += "		 FPriceDecimal smallint null,                                                                                             ";
		sql += "		 FOutQty Decimal(28,10) null,                                                                                             ";
		sql += "		 FOutAmount Decimal(32,10) null,                                                                                          ";
		sql += "		 FConsignAmount Decimal(32,10) NUll,                                                                                      ";
		sql += "		 FConsignPrice  Decimal(32,10) Null,                                                                                      ";
		sql += "		 FCommitQty Decimal(28,10) null,                                                                                          ";
		sql += "		 FCommitAmount Decimal(32,10) null,                                                                                       ";
		sql += "		 FNotCommitAmount Decimal(32,10) null,                                                                                    ";
		sql += "		 FNotCommitQty Decimal(28,10) null,                                                                                       ";
		sql += "		 FCUUnitName  varchar(355) null,                                                                                          ";
		sql += "		 FCUUnitOutQty Decimal(28,10) null,                                                                                       ";
		sql += "		 FCUUnitCommitQty Decimal(28,10) null,                                                                                    ";
		sql += "		 FCUUnitNotCommitQty Decimal(28,10) null,                                                                                 ";
		sql += "		 FSumSort smallint not null Default(0),                                                                                   ";
		sql += "		 FID int IDENTITY )                                                                                                       ";
		sql += "	INSERT INTO #DATA                                                                                                             ";
		sql += "	SELECT tt1.FName1,t1.FNumber,'','','','',6,4,ROUND(SUM(ISNULL(V1.FOUTQTY,0)),6),sum(isnull(v1.FOutAmount,0)),                 ";
		sql += "	 sum(Isnull(v1.FConsignAmount,0)), CASE WHEN ROUND(SUM(ISNULL(V1.FOUTQTY,0)),6)=0 THEN 0 ELSE                                 ";
		sql += "	 sum(isnull(v1.FConsignAmount,0)) / ROUND(SUM(ISNULL(V1.FOUTQTY,0)),6) END,                                                   ";
		sql += "	 sum(isnull(v1.FCommitQty,0)),sum(isnull(v1.FCommitAmount,0)),0,0,                                                            ";
		sql += "	'',                                                                                                                           ";
		sql += "	 SUM(ISNULL(v1.FOutQty,0)/(Case When ISNULL(t3.FCoefficient,0)=0 Then 1 Else t3.FCoefficient End )),                          ";
		sql += "	 SUM(ISNULL(v1.FCommitQty,0)/(Case When ISNULL(t3.FCoefficient,0)=0 Then 1 Else t3.FCoefficient End )),                       ";
		sql += "	 0, CASE  WHEN   Grouping(tt1.FName1)=1 THEN 106                                                                              ";
		sql += "	  WHEN   Grouping(t1.FNumber)=1 THEN 107  ELSE   0 END                                                                        ";
		sql += "	 From #DATA2 v1                                                                                                               ";
		sql += "	 Inner Join RemoteServer.AIS2009.dbo.t_ICItem t1 On v1.FItemID=t1.FItemID                                                                              ";
		sql += "	 Inner Join RemoteServer.AIS2009.dbo.t_Organization t2 On v1.FCustID=t2.FItemID                                                                        ";
		sql += "	 Inner Join RemoteServer.AIS2009.dbo.T_MeasureUnit t3 On t1.FSaleUnitID = t3.FMeasureUnitID                                                            ";
		sql += "	 Inner Join #ItemLevel tt1                                                                                                    ";
		sql += "                                                                                                                                ";
		sql += "	 On t1.FItemID=tt1.FItemID WHERE 1=1                                                                                          ";
		sql += "                                                                                                                                ";
		sql += "	 Group By tt1.FName1,t1.FNumber WITH ROLLUP                                                                                   ";
		sql += "                                                                                                                                ";
		sql += "	 Update #DATA set  FNotCommitAmount = isnull(FOutAmount,0) - isnull(FCommitAmount,0),FNotCommitQty = FOutQty-                 ";
		sql += "	 (case when FCommitQty is null then 0 else FCommitQty end )                                                                   ";
		sql += "	 Update #DATA set  FCUUnitNotCommitQty = FCUUnitOutQty-(case when FCUUnitCommitQty is null then 0 else FCUUnitCommitQty end ) ";
		sql += "	Drop Table #DATA2                                                                                                              ";
		sql += "                                                                                                                                 ";
		sql += "	 Update t1 Set t1.FName=t2.FName,t1.FShortNumber=t2.FShortNumber,t1.FModel=t2.FModel, t1.FCUUnitName = t4.FName,   ";
		sql += " t1.FUnitName=t3.FName,t1.FQtyDecimal=t2.FQtyDecimal,t1.FPriceDecimal=t2.FPriceDecimal  From #Data t1,RemoteServer.AIS2009.dbo.t_ICItem t2,RemoteServer.AIS2009.dbo.t_MeasureUnit t3, ";
		sql += " RemoteServer.AIS2009.dbo.t_MeasureUnit t4  Where t1.FNumber=t2.FNumber  And t2.FUnitGroupID=t3.FUnitGroupID  And t2.FSaleUnitID=t4.FMeasureUnitID ";
		sql += "	 And t3.FStandard=1 ";
		sql += "	Update #Data Set  FName1=FName1+'(小计)'  WHERE FSumSort=107 ";
		sql += "	Update #Data Set FName1='合计' WHERE FSumSort=106 ";
		sql += "	Update #Data Set FSumSort=101   WHERE FSumSort=106 ";
		sql += "	SELECT FName1,isnull(FNumber,'') AS FNumber,FName,FModel,FCUUnitName,Convert(decimal(18,4),FCUUnitOutQty) AS FCUUnitOutQty,Convert(decimal(18,5),FConsignPrice)*1000 AS FConsignPrice,Convert(decimal(18,5),FConsignAmount) AS FConsignAmount FROM #DATA   ";
		sql += "	 Order by FID  ";
		sql += "	DROP TABLE #DATA ";
		sql += "	DROP TABLE #ItemLevel ";

		List<Map> list = saleMiniDao.execProcedureByIt(sql);

		// List<JeecgMinidaoEntity> list =
		// jeecgMinidaoService.listAll(jeecgMinidao, dataGrid.getPage(),
		// dataGrid.getRows());

		dataGrid.setTotal(list.size());
		dataGrid.setResults(list);
		TagUtil.datagrid(response, dataGrid);
	}
    
	
	
	/**
	 * 
	 * 導出 销售出库汇总 依产品
	 * 
	 * @param request
	 * @param response
	 * @param dataGrid
	 */
	@RequestMapping(params = "export_itlist")
	public void export_itlist(HttpServletRequest request, HttpServletResponse response, DataGrid dataGrid) {

	
		String FDate_begin = request.getParameter("FDate_begin");
		String FDate_end = request.getParameter("FDate_end");
		String FNumber = request.getParameter("FNumber");
		String FName = request.getParameter("FName");
		String FModel = request.getParameter("FModel");

		String sql = "	SET NOCOUNT ON                                                                                                                ";
		sql += "	 Create Table #DATA2 (      FIndex int IDENTITY,                                                                              ";
		sql += "		 FItemID int null,                                                                                                        ";
		sql += "		 FCustID int null,                                                                                                        ";
		sql += "		 FOutQty float null,                                                                                                      ";
		sql += "		 FOutAmount money null,                                                                                                   ";
		sql += "		 FConsignAmount money null,                                                                                               ";
		sql += "		 FConsignPrice  Money Null,                                                                                               ";
		sql += "		 FCommitQty float null,                                                                                                   ";
		sql += "		 FCommitAmount money null)                                                                                                ";
		sql += "	Insert Into #DATA2 (FItemID,FCustID,FOutQty,FOutAmount,FConsignAmount,FConsignPrice)                                          ";
		sql += "	Select v2.FItemID, v1.FSupplyID, v2.FQty,v2.FAmount,v2.FConsignAmount,v2.FConsignPrice                                        ";
		sql += "	From RemoteServer.AIS2009.dbo.ICStockBill v1                                                                                                           ";
		sql += "	Join RemoteServer.AIS2009.dbo.ICStockBillEntry v2 On v1.FInterID = v2.FInterID                                                                         ";
		sql += "	Join RemoteServer.AIS2009.dbo.t_ICItem t1 On v2.FItemID = t1.FItemID                                                                                   ";
		sql += "	Left Join RemoteServer.AIS2009.dbo.t_Organization t2 On v1.FSupplyID=t2.FItemID                                                                        ";
		sql += "                                                                                                                                ";
		sql += "	 Where v1.FTranType =21                                                                                                       ";
		sql += "	  AND v1.FStatus>0 And v1.FCancelLation=0  AND v1.FROB=1                                                                      ";
		// sql +=
		// "	  AND convert(datetime,convert(varchar(30),v1.FDate,111)) >='2014-07-01'                                                      ";
		// sql +=
		// "	 AND convert(datetime,convert(varchar(30),v1.FDate,111)) <='2014-07-30'                                                       ";
		if (StringUtil.isNotEmpty(FDate_begin) && StringUtil.isNotEmpty(FDate_end)) {
			sql += " AND v1.Fdate between " + "'" + FDate_begin + "'" + " and " + "'" + FDate_end + "'";
		}
		if (StringUtil.isNotEmpty(FNumber)) {
			sql += " and t1.FNumber like " + "'%" + FNumber + "%'";
		}
		if (StringUtil.isNotEmpty(FName)) {
			sql += " and t1.FName like " + "'%" + FName + "%'";
		}
		if (StringUtil.isNotEmpty(FModel)) {
			sql += " and t1.FModel = " + "'" + FModel + "'";
		}
		sql += "	 Insert Into #DATA2 (FItemID,FCustID,FCommitQty,FCommitAmount)                                                                ";
		sql += "	 Select v2.FItemID,v1.FCustID,v2.FQty, Round(case v1.FTranType when 80 then v2.FAmount * isnull(v1.FExchangeRate,1) else      ";
		sql += "	 (v2.FAmount - v2.FTaxAmount)* isnull(v1.FExchangeRate,1)  end,2)                                                             ";
		sql += "	 FROM  RemoteServer.AIS2009.dbo.ICSale v1                                                                                                              ";
		sql += "	Join RemoteServer.AIS2009.dbo.ICSaleEntry v2 On v1.FInterID = v2.FInterID                                                                              ";
		sql += "	Join RemoteServer.AIS2009.dbo.t_ICItem t1 On v2.FItemID = t1.FItemID                                                                                   ";
		sql += "	Left Join RemoteServer.AIS2009.dbo.t_Organization t2 On v1.FCustID = t2.FItemID                                                                        ";
		sql += "                                                                                                                                ";
		sql += "	 Where v1.FTranType in (80,86)                                                                                                ";
		sql += "	  AND v1.FStatus>0 And v1.FCancelLation=0  AND v1.FROB=1                                                                      ";
		if (StringUtil.isNotEmpty(FDate_begin) && StringUtil.isNotEmpty(FDate_end)) {
			sql += " AND v1.Fdate between " + "'" + FDate_begin + "'" + " and " + "'" + FDate_end + "'";
		}
		if (StringUtil.isNotEmpty(FNumber)) {
			sql += " and t1.FNumber like " + "'%" + FNumber + "%'";
		}
		if (StringUtil.isNotEmpty(FName)) {
			sql += " and t1.FName like " + "'%" + FName + "%'";
		}
		if (StringUtil.isNotEmpty(FModel)) {
			sql += " and t1.FModel = " + "'" + FModel + "'";
		}
		sql += "	CREATE TABLE #ItemLevel(                                                                                                      ";
		sql += "	 FNumber1 Varchar(355),                                                                                                       ";
		sql += "	 FName1 Varchar(355),                                                                                                         ";
		sql += "	 FItemID int,                                                                                                                 ";
		sql += "	 FNumber VARCHAR(355))                                                                                                        ";
		sql += "	 Insert Into #ItemLevel SELECT  ";
		sql += "	 Case When CharIndex('.',FFullNumber)-1= -1 or FLevel<2 THEN NULL ELSE SUBSTRING(FNumber, 1,CharIndex('.',FFullNumber)-1) END,";
		sql += "	 '',                                                                                                                          ";
		sql += "	 FItemID,FNumber From RemoteServer.AIS2009.dbo.t_Item                                                                                                  ";
		sql += "	 Where FItemClassID=4                                                                                                         ";
		sql += "	 AND FDetail=1  And  FItemID In (SELECT DISTINCT  FItemID  FROM #DATA2 )                                                      ";
		sql += "	 Update t0 SET t0.FName1=t1.FName                                                                                             ";
		sql += "	 From #ItemLevel t0 left join RemoteServer.AIS2009.dbo.t_Item t1 On t0.FNumber1=t1.FNumber  AND t1.FItemClassID=4 AND t1.FDetail=0                     ";
		sql += "                                                                                                                                ";
		sql += "	CREATE TABLE #DATA(                                                                                                           ";
		sql += "	FName1 varchar(355) Null,                                                                                                     ";
		sql += "		   FNumber  varchar(355) null,                                                                                            ";
		sql += "		 FShortNumber  varchar(355) null,                                                                                         ";
		sql += "		 FName  varchar(355) null,                                                                                                ";
		sql += "		 FModel  varchar(355) null,                                                                                               ";
		sql += "		 FUnitName  varchar(355) null,                                                                                            ";
		sql += "		 FQtyDecimal smallint null,                                                                                               ";
		sql += "		 FPriceDecimal smallint null,                                                                                             ";
		sql += "		 FOutQty Decimal(28,10) null,                                                                                             ";
		sql += "		 FOutAmount Decimal(32,10) null,                                                                                          ";
		sql += "		 FConsignAmount Decimal(32,10) NUll,                                                                                      ";
		sql += "		 FConsignPrice  Decimal(32,10) Null,                                                                                      ";
		sql += "		 FCommitQty Decimal(28,10) null,                                                                                          ";
		sql += "		 FCommitAmount Decimal(32,10) null,                                                                                       ";
		sql += "		 FNotCommitAmount Decimal(32,10) null,                                                                                    ";
		sql += "		 FNotCommitQty Decimal(28,10) null,                                                                                       ";
		sql += "		 FCUUnitName  varchar(355) null,                                                                                          ";
		sql += "		 FCUUnitOutQty Decimal(28,10) null,                                                                                       ";
		sql += "		 FCUUnitCommitQty Decimal(28,10) null,                                                                                    ";
		sql += "		 FCUUnitNotCommitQty Decimal(28,10) null,                                                                                 ";
		sql += "		 FSumSort smallint not null Default(0),                                                                                   ";
		sql += "		 FID int IDENTITY )                                                                                                       ";
		sql += "	INSERT INTO #DATA                                                                                                             ";
		sql += "	SELECT tt1.FName1,t1.FNumber,'','','','',6,4,ROUND(SUM(ISNULL(V1.FOUTQTY,0)),6),sum(isnull(v1.FOutAmount,0)),                 ";
		sql += "	 sum(Isnull(v1.FConsignAmount,0)), CASE WHEN ROUND(SUM(ISNULL(V1.FOUTQTY,0)),6)=0 THEN 0 ELSE                                 ";
		sql += "	 sum(isnull(v1.FConsignAmount,0)) / ROUND(SUM(ISNULL(V1.FOUTQTY,0)),6) END,                                                   ";
		sql += "	 sum(isnull(v1.FCommitQty,0)),sum(isnull(v1.FCommitAmount,0)),0,0,                                                            ";
		sql += "	'',                                                                                                                           ";
		sql += "	 SUM(ISNULL(v1.FOutQty,0)/(Case When ISNULL(t3.FCoefficient,0)=0 Then 1 Else t3.FCoefficient End )),                          ";
		sql += "	 SUM(ISNULL(v1.FCommitQty,0)/(Case When ISNULL(t3.FCoefficient,0)=0 Then 1 Else t3.FCoefficient End )),                       ";
		sql += "	 0, CASE  WHEN   Grouping(tt1.FName1)=1 THEN 106                                                                              ";
		sql += "	  WHEN   Grouping(t1.FNumber)=1 THEN 107  ELSE   0 END                                                                        ";
		sql += "	 From #DATA2 v1                                                                                                               ";
		sql += "	 Inner Join RemoteServer.AIS2009.dbo.t_ICItem t1 On v1.FItemID=t1.FItemID                                                                              ";
		sql += "	 Inner Join RemoteServer.AIS2009.dbo.t_Organization t2 On v1.FCustID=t2.FItemID                                                                        ";
		sql += "	 Inner Join RemoteServer.AIS2009.dbo.T_MeasureUnit t3 On t1.FSaleUnitID = t3.FMeasureUnitID                                                            ";
		sql += "	 Inner Join #ItemLevel tt1                                                                                                    ";
		sql += "                                                                                                                                ";
		sql += "	 On t1.FItemID=tt1.FItemID WHERE 1=1                                                                                          ";
		sql += "                                                                                                                                ";
		sql += "	 Group By tt1.FName1,t1.FNumber WITH ROLLUP                                                                                   ";
		sql += "                                                                                                                                ";
		sql += "	 Update #DATA set  FNotCommitAmount = isnull(FOutAmount,0) - isnull(FCommitAmount,0),FNotCommitQty = FOutQty-                 ";
		sql += "	 (case when FCommitQty is null then 0 else FCommitQty end )                                                                   ";
		sql += "	 Update #DATA set  FCUUnitNotCommitQty = FCUUnitOutQty-(case when FCUUnitCommitQty is null then 0 else FCUUnitCommitQty end ) ";
		sql += "	Drop Table #DATA2                                                                                                              ";
		sql += "                                                                                                                                 ";
		sql += "	 Update t1 Set t1.FName=t2.FName,t1.FShortNumber=t2.FShortNumber,t1.FModel=t2.FModel, t1.FCUUnitName = t4.FName,   ";
		sql += " t1.FUnitName=t3.FName,t1.FQtyDecimal=t2.FQtyDecimal,t1.FPriceDecimal=t2.FPriceDecimal  From #Data t1,RemoteServer.AIS2009.dbo.t_ICItem t2,RemoteServer.AIS2009.dbo.t_MeasureUnit t3, ";
		sql += " RemoteServer.AIS2009.dbo.t_MeasureUnit t4  Where t1.FNumber=t2.FNumber  And t2.FUnitGroupID=t3.FUnitGroupID  And t2.FSaleUnitID=t4.FMeasureUnitID ";
		sql += "	 And t3.FStandard=1 ";
		sql += "	Update #Data Set  FName1=FName1+'(小计)'  WHERE FSumSort=107 ";
		sql += "	Update #Data Set FName1='合计' WHERE FSumSort=106 ";
		sql += "	Update #Data Set FSumSort=101   WHERE FSumSort=106 ";
		sql += "	SELECT FName1,isnull(FNumber,'') AS FNumber,FName,FModel,FCUUnitName,Convert(decimal(18,4),FCUUnitOutQty) AS FCUUnitOutQty,Convert(decimal(18,5),FConsignPrice)*1000 AS FConsignPrice,Convert(decimal(18,5),FConsignAmount) AS FConsignAmount FROM #DATA   ";
		sql += "	 Order by FID  ";
		sql += "	DROP TABLE #DATA ";
		sql += "	DROP TABLE #ItemLevel ";                                                           
		
	//	List<Map> list = saleMiniDao.execContract(sql);

	//	List<Map<String, Object>> maplist = null;
    	List<Map> maplist = null;

		try {
		   maplist = saleMiniDao.execProcedureByIt(sql);

			//maplist = systemService.findForJdbc(sql);
			List<String[]> list = new ArrayList<String[]>();

			for (Map<String, Object> map : maplist) {
				String[] arr = new String[map.size()];
				arr[0] = (String) map.get("FName1");
				arr[1] = (String) map.get("FNumber");
				arr[2] = (String) map.get("FName");
				arr[3] = (String) map.get("FModel");
				arr[4] =  ((String) map.get("FCUUnitName"));
				arr[5] = String.valueOf((BigDecimal) map.get("FCUUnitOutQty"));
				arr[6] = String.valueOf((BigDecimal) map.get("FConsignPrice"));
				arr[7] = String.valueOf((BigDecimal) map.get("FConsignAmount"));
				list.add(arr);

			}

			String[] columnNames = new String[] { "物料类别", "物料代码", "物料名称", "厚度", "单位", "数量", "单价", "金额"};
			String titleName = "销售出库汇总（依产品）.xls";

			OutputStream fOut = response.getOutputStream();
			response.setHeader("Content-Disposition", "attachment; filename="
					+ new String(titleName.getBytes("GB2312"), "ISO8859-1"));

			ExcelUtils.create(request, list, columnNames, titleName, fOut);

		} catch (Exception ex) {

		}

	}
	
	
	
	
	
	
	
	/**
	 * easyui AJAX请求数据 合同执行情况
	 * 
	 * @param request
	 * @param response
	 * @param dataGrid
	 * @param user
	 */

	@RequestMapping(params = "datagrid")
	public void datagrid(HttpServletRequest request, HttpServletResponse response, DataGrid dataGrid) {

		String FDate_begin = request.getParameter("FDate_begin");
		String FDate_end = request.getParameter("FDate_end");
		String FCustName = request.getParameter("FCustName");
		String FName = request.getParameter("FName");
		String FContractNo = request.getParameter("FContractNo");
		
		
		//sql+="	-- 销售合同执行情况 new                                                                                                                                                           ";
		String sql="	DECLARE @RQ1 SMALLDATETIME                                                                                                                                                        ";
		sql+="	DECLARE @RQ2 SMALLDATETIME                                                                                                                                                        ";
		sql+="                                                                                                                                                                                    ";
		sql+="	DECLARE @RQDAY1_Y SMALLDATETIME                                                                                                                                                   ";
		sql+="	DECLARE @RQDAY1_M SMALLDATETIME                                                                                                                                                   ";
		sql+="	DECLARE @RQDAY1_L1 SMALLDATETIME                                                                                                                                                  ";
		sql+="	DECLARE @RQDAY1_L2 SMALLDATETIME                                                                                                                                                  ";
		sql+="                                                                                                                                                                                    ";
		sql+="                                                                                                                                                                                    ";
		//sql+="	--日期参数                                                                                                                                                                        ";
	//	sql+="	SET @RQ1='2014-12-01'  --开始时间                                                                                                                                                 ";
		//sql+="	SET @RQ2='2016-01-19'  --报表查询时间                                                                                                                                             ";
		
		sql += " SET @RQ1='" + FDate_begin + "'";
		sql += " SET @RQ2='" + FDate_end + "'";
		
		
		sql+="                                                                                                                                                                                    ";
		sql+="                                                                                                                                                                                    ";
		sql+="                                                                                                                                                                                    ";
		//sql+="	--本年第一天                                                                                                                                                                      ";
		sql+="	SELECT @RQDAY1_Y=  DATEADD(yy,   DATEDIFF(yy,0,@RQ2),   0)                                                                                                                        ";
		//sql+="	--本月第一天                                                                                                                                                                      ";
		sql+="	SELECT @RQDAY1_M=  CONVERT(CHAR(10),DATEADD(dd,-DAY(@RQ2)+1,@RQ2),111)                                                                                                            ";
		sql+="                                                                                                                                                                                    ";
		sql+="                                                                                                                                                                                    ";
		//sql+="	--上月第一天                                                                                                                                                                      ";
		sql+="	                                                                                                                                                                                  ";
		sql+="	SELECT @RQDAY1_L1= CONVERT(CHAR(10),DATEADD(month,-1,DATEADD(dd,-DAY(@RQ2)+1,@RQ2)),111)                                                                                          ";
		//sql+="	--上月最后一天                                                                                                                                                                    ";
		sql+="	SELECT @RQDAY1_L2=CONVERT(CHAR(10),DATEADD(ms,-3,DATEADD(mm, DATEDIFF(mm,0,@RQ2),0)),111)+' 00:00:00'                                                                             ";
		sql+="                                                                                                                                                                                    ";
		sql+="	select distinct U1.* from (                                                                                                                                                                ";
		//sql+="	--查询基础开始时间未执行完的合同                                                                                                                                                  ";
		sql +="	SELECT V1.*,Convert(decimal(18,3),isnull(v3.FQTY_Contract,0)) as BYQDL,Convert(decimal(18,3),ISNULL(V2.FQTY_Current,0)) AS SYQJL,Convert(decimal(18,3),isnull(V4.FQTY_STOCKOUT,0)) AS BYFCL, ";
		sql +="					Convert(decimal(18,3),case when ISNULL(V2.FQTY_Current,0)=0 THEN ROUND(ISNULL(V1.HTL,0)-isnull(V4.FQTY_STOCKOUT,0),3) ELSE ";
		sql +="							ROUND(ISNULL(V2.FQTY_Current,0)-isnull(V4.FQTY_STOCKOUT,0),3) END )  BYQJL FROM (  ";
		sql +="		SELECT t4.fname as fCustName,T1.FContractID,T1.FEntryID,t1.FContractNo as FContractNo,t1.fdate as FDate,T1.FProductID,  ";
		sql +="			   T3.FName,T3.FModel,T3.F_101 AS fGrade,T3.F_102 AS fHard,t1.FTaxPrice,  ";
		sql +="			   Convert(decimal(18,3),T1.FQTY_Contract) as HTL  ";
		sql+="		   FROM (                                                                                                                                                                     ";
		sql+="	select T1.FContractID,t1.FCUSTOMER,t1.fdate,T2.FEntryID,t1.FContractNo,t2.FProductID,SUM(T2.FQuantity) AS FQTY_Contract,T2.FTaxPrice                                              ";
		sql+="							 from RemoteServer.AIS2009.dbo.t_rpContract t1 Inner Join RemoteServer.AIS2009.dbo.t_rpContractEntry t2 on t1.FContractID=t2.FContractID                  ";
		sql+="							 WHERE T1.FDate>=@RQ1 and t1.FDate<@RQ2+1                                                                                                                 ";
		sql+="							                                                                                                                                                          ";
		sql+="							GROUP BY T1.FContractID,T1.FCUSTOMER,t1.fdate,T2.FEntryID,T1.FContractNo,T2.FProductID,T2.FTaxPrice                                                       ";
		sql+="					) T1 LEFT JOIN (                                                                                                                                                  ";
		sql+="                                                                                                                                                                                    ";
		sql+="	SELECT T2.FContractInterID,T2.FContractEntryID,FITEMID,SUM(FQTY)/1000 AS FQTY_STOCKOUT                                                                                            ";
		sql+="							FROM RemoteServer.AIS2009.dbo.ICStockBill T1 INNER JOIN RemoteServer.AIS2009.dbo.ICStockBillEntry T2                                                      ";
		sql+="							ON T1.FInterID=T2.FInterID                                                                                                                                ";
		sql+="							WHERE T1.FTranType=21 and  T1.FDate>=@RQ1 and t1.FDate<@RQ2+1                                                                                             ";
		sql+="							GROUP BY FContractInterID,FContractEntryID,FItemID                                                                                                        ";
		sql+="									) T2 ON T1.FContractID=T2.FContractInterID AND T1.FEntryID=T2.FContractEntryID                                                                    ";
		sql+="										 LEFT JOIN RemoteServer.AIS2009.dbo.t_ICItem  T3 ON T1.FProductID=T3.FItemID                                                                  ";
		sql+="										 LEFT JOIN RemoteServer.AIS2009.dbo.t_Organization t4 on t1.FCUSTOMER=t4.FItemID                                                              ";
		sql+="										 WHERE Round(ISNULL(T1.FQTY_Contract,0)-ISNULL(FQTY_STOCKOUT,0),3)>0                                                                          ";
		sql+="								) V1 LEFT JOIN                                                                                                                                        ";
		sql+="							                                                                                                                                                          ";
		sql+="									                                                                                                                                                  ";
		sql+="											                                                                                                                                          ";
		sql+="                                                                                                                                                                                    ";
		//sql+="	--上月的合同(应该是非本月合同）                                                                                                                                                   ";
		sql+="							(                                                                                                                                                         ";
		sql+="	SELECT t4.fname as fCustName,T1.FContractID,T1.FEntryID,t1.FContractNo,T1.FProductID,                                                                                             ";
		sql+="		   T3.FName,T3.FModel,T3.F_101 AS fGrade,T3.F_102 AS fHard,t1.FTaxPrice,                                                                                                      ";
		sql+="		   T1.FQTY_Contract ,ROUND(ISNULL(T2.FQTY_STOCKOUT,0),3) AS FQTY_STOCKOUT,                                                                                                    ";
		sql+="		   Case when Round(ISNULL(T1.FQTY_Contract,0)-ISNULL(FQTY_STOCKOUT,0),3)<0 then 0 else                                                                                        ";
		sql+="					 Round(ISNULL(T1.FQTY_Contract,0)-ISNULL(FQTY_STOCKOUT,0),3) end AS FQTY_Current                                                                                  ";
		sql+="		   FROM (                                                                                                                                                                     ";
		sql+="	select T1.FContractID,t1.FCUSTOMER,T2.FEntryID,t1.FContractNo,t2.FProductID,SUM(T2.FQuantity) AS FQTY_Contract,T2.FTaxPrice                                                       ";
		sql+="							 from RemoteServer.AIS2009.dbo.t_rpContract t1 Inner Join RemoteServer.AIS2009.dbo.t_rpContractEntry t2 on t1.FContractID=t2.FContractID                  ";
		sql+="							 WHERE  t1.FDate<@RQDAY1_L2+1                                                                                                                             ";
		sql+="							                                                                                                                                                          ";
		sql+="							GROUP BY T1.FContractID,T1.FCUSTOMER,T2.FEntryID,T1.FContractNo,T2.FProductID,T2.FTaxPrice                                                                ";
		sql+="					) T1 LEFT JOIN (                                                                                                                                                  ";
		sql+="                                                                                                                                                                                    ";
		sql+="	SELECT T2.FContractInterID,T2.FContractEntryID,FITEMID,SUM(FQTY)/1000 AS FQTY_STOCKOUT                                                                                            ";
		sql+="							FROM RemoteServer.AIS2009.dbo.ICStockBill T1 INNER JOIN RemoteServer.AIS2009.dbo.ICStockBillEntry T2                                                      ";
		sql+="							ON T1.FInterID=T2.FInterID                                                                                                                                ";
		sql+="							WHERE T1.FTranType=21 and   t1.FDate<@RQDAY1_L2+1                                                                                                         ";
		sql+="							GROUP BY FContractInterID,FContractEntryID,FItemID                                                                                                        ";
		sql+="									) T2 ON T1.FContractID=T2.FContractInterID AND T1.FEntryID=T2.FContractEntryID                                                                    ";
		sql+="										 LEFT JOIN RemoteServer.AIS2009.dbo.t_ICItem  T3 ON T1.FProductID=T3.FItemID                                                                  ";
		sql+="										 LEFT JOIN RemoteServer.AIS2009.dbo.t_Organization t4 on t1.FCUSTOMER=t4.FItemID 									                          ";
		sql+="											                                                                                                                                          ";
		sql+="								) V2 ON V1.FContractID=V2.FContractID AND V1.FEntryID=V2.FEntryID		                                                                              ";
		sql+="											                                                                                                                                          ";
		sql+="						                                                                                                                                                              ";
		sql+="						                                                                                                                                                              ";
		sql+="								LEFT JOIN                                                                                                                                             ";
		sql+="                                                                                                                                                                                    ";
		//sql+="	--本月的合同                                                                                                                                                                      ";
		sql+="				(select T1.FContractID,t1.FCUSTOMER,T2.FEntryID,t1.FContractNo,t2.FProductID,SUM(T2.FQuantity) AS FQTY_Contract,T2.FTaxPrice                                          ";
		sql+="							 from RemoteServer.AIS2009.dbo.t_rpContract t1 Inner Join RemoteServer.AIS2009.dbo.t_rpContractEntry t2 on t1.FContractID=t2.FContractID                  ";
		sql+="							 WHERE T1.FDate>=@RQDAY1_M and t1.FDate<@RQ2+1                                                                                                            ";
		sql+="							                                                                                                                                                          ";
		sql+="							GROUP BY T1.FContractID,T1.FCUSTOMER,T2.FEntryID,T1.FContractNo,T2.FProductID,T2.FTaxPrice								                                  ";
		sql+="											                                                                                                                                          ";
		sql+="							) V3                                                                                                                                                      ";
		sql+="									 ON V1.FContractID=V3.FContractID AND V1.FEntryID=V3.FEntryID                                                                                     ";
		sql+="									                                                                                                                                                  ";
		sql+="								LEFT JOIN                                                                                                                                             ";
		sql+="					                                                                                                                                                                  ";
		sql+="				(SELECT T2.FContractInterID AS FContractID,T2.FContractEntryID AS FEntryID ,FITEMID,Convert(decimal(18,3),SUM(FQTY)/1000) AS FQTY_STOCKOUT                            ";
		sql+="							FROM RemoteServer.AIS2009.dbo.ICStockBill T1 INNER JOIN RemoteServer.AIS2009.dbo.ICStockBillEntry T2                                                      ";
		sql+="							ON T1.FInterID=T2.FInterID                                                                                                                                ";
		sql+="							WHERE T1.FTranType=21 and  T1.FDate>=@RQDAY1_M and t1.FDate<@RQ2+1                                                                                        ";
		sql+="							GROUP BY FContractInterID,FContractEntryID,FItemID                                                                                                        ";
		sql+="									) V4 ON V1.FContractID=V4.FContractID AND V1.FEntryID=V4.FEntryID                                                                                 ";
		//sql+="	)U1 left join RemoteServer.AIS2009.dbo.SEOrderEntry U2 on U2.FItemID  = U1.FProductID  and U1.FContractID = U2.FSourceInterId   and U1.HTL = U2.FAuxQty                                                   ";
		sql+="	)U1  where 1=1  ";
	
		sql += "  and U1.FProductID  in ( select  FItemID from  RemoteServer.AIS2009.dbo.SEOrderEntry u2 where u2.FMRPClosed = 0)	";
		
		
		/*ql+= "	(  ";
			sql+= "				  SELECT distinct  FMRPClosed,FSourceInterId,FContractBillNo,FItemID,FAuxQTY  FROM RemoteServer.AIS2009.dbo.SEOrderEntry  ";
					sql+= "				) ";

							sql+= "			 U2 on U2.FItemID  = U1.FProductID  and U1.FContractID = U2.FSourceInterId and U1.HTL = U2.FAuxQty ";
									sql+= "			 where  U2.FMRPClosed != 1	 ";
											sql+= "			 and len(U1.fcustName) > 0	 ";*/
		
		//sql+="	 where  U2.FMRPClosed != 0 	 AND U2.FMRPAutoClosed != 0                                                                                                                                                  ";
		//sql+="	 and len(U1.fcustName) > 0					                                                                                                                                      ";
		
		
		if (StringUtil.isNotEmpty(FName)) {
			sql += " and U1.FName like " + "'%" + FName + "%'";
		}
		if (StringUtil.isNotEmpty(FCustName)) {
			sql += " and U1.FCustName like " + "'%" + FCustName + "%'";
		}

		if (StringUtil.isNotEmpty(FContractNo)) {
			sql += " and U1.FContractNo like " + "'%" + FContractNo + "%'";
		}
		
		sql += " order by U1.FDate ";
		
		List<Map> list = saleMiniDao.execContract(sql);

		// List<JeecgMinidaoEntity> list =
		// jeecgMinidaoService.listAll(jeecgMinidao, dataGrid.getPage(),
		// dataGrid.getRows());

		dataGrid.setTotal(list.size());
		dataGrid.setResults(list);
		
		   // 数量合计
				BigDecimal sum_qty = new BigDecimal("0.0000");
				BigDecimal sum_qty2 = new BigDecimal("0.0000");
				BigDecimal sum_qty3 = new BigDecimal("0.0000");
				BigDecimal sum_qty4 = new BigDecimal("0.0000");
				BigDecimal sum_qty5 = new BigDecimal("0.0000");
				
				for (Map<String, Object> map : list)
				{
				   sum_qty = sum_qty.add((BigDecimal) map.get("HTL"));
				   sum_qty2 = sum_qty2.add((BigDecimal) map.get("BYQDL"));
				   sum_qty3 = sum_qty3.add((BigDecimal) map.get("SYQJL"));
				   sum_qty4 = sum_qty4.add((BigDecimal) map.get("BYFCL"));
				   sum_qty5 = sum_qty5.add((BigDecimal) map.get("BYQJL"));
				}
				
				dataGrid.setFooter("FCustName:合计" + ",HTL:" + sum_qty+ ",BYQDL:" + sum_qty2+ ",SYQJL:" + sum_qty3+ ",BYFCL:" + sum_qty4+ ",BYQJL:" + sum_qty5);
		
		
		TagUtil.datagrid(response, dataGrid);
	}

	/**
	 * 
	 * 導出 合同执行情况
	 * 
	 * @param request
	 * @param response
	 * @param dataGrid
	 */
	@RequestMapping(params = "export_htzx")
	public void export_htzx(HttpServletRequest request, HttpServletResponse response, DataGrid dataGrid) {

	
		String FDate_begin = request.getParameter("FDate_begin");
		String FDate_end = request.getParameter("FDate_end");
		String FCustName = request.getParameter("FCustName");
		String FName = request.getParameter("FName");
		String FContractNo = request.getParameter("FContractNo");
		
		
		//sql+="	-- 销售合同执行情况 new                                                                                                                                                           ";
		String sql="	DECLARE @RQ1 SMALLDATETIME                                                                                                                                                        ";
		sql+="	DECLARE @RQ2 SMALLDATETIME                                                                                                                                                        ";
		sql+="                                                                                                                                                                                    ";
		sql+="	DECLARE @RQDAY1_Y SMALLDATETIME                                                                                                                                                   ";
		sql+="	DECLARE @RQDAY1_M SMALLDATETIME                                                                                                                                                   ";
		sql+="	DECLARE @RQDAY1_L1 SMALLDATETIME                                                                                                                                                  ";
		sql+="	DECLARE @RQDAY1_L2 SMALLDATETIME                                                                                                                                                  ";
		sql+="                                                                                                                                                                                    ";
		sql+="                                                                                                                                                                                    ";
		//sql+="	--日期参数                                                                                                                                                                        ";
	//	sql+="	SET @RQ1='2014-12-01'  --开始时间                                                                                                                                                 ";
		//sql+="	SET @RQ2='2016-01-19'  --报表查询时间                                                                                                                                             ";
		
		sql += " SET @RQ1='" + FDate_begin + "'";
		sql += " SET @RQ2='" + FDate_end + "'";
		
		
		sql+="                                                                                                                                                                                    ";
		sql+="                                                                                                                                                                                    ";
		sql+="                                                                                                                                                                                    ";
		//sql+="	--本年第一天                                                                                                                                                                      ";
		sql+="	SELECT @RQDAY1_Y=  DATEADD(yy,   DATEDIFF(yy,0,@RQ2),   0)                                                                                                                        ";
		//sql+="	--本月第一天                                                                                                                                                                      ";
		sql+="	SELECT @RQDAY1_M=  CONVERT(CHAR(10),DATEADD(dd,-DAY(@RQ2)+1,@RQ2),111)                                                                                                            ";
		sql+="                                                                                                                                                                                    ";
		sql+="                                                                                                                                                                                    ";
		//sql+="	--上月第一天                                                                                                                                                                      ";
		sql+="	                                                                                                                                                                                  ";
		sql+="	SELECT @RQDAY1_L1= CONVERT(CHAR(10),DATEADD(month,-1,DATEADD(dd,-DAY(@RQ2)+1,@RQ2)),111)                                                                                          ";
		//sql+="	--上月最后一天                                                                                                                                                                    ";
		sql+="	SELECT @RQDAY1_L2=CONVERT(CHAR(10),DATEADD(ms,-3,DATEADD(mm, DATEDIFF(mm,0,@RQ2),0)),111)+' 00:00:00'                                                                             ";
		sql+="                                                                                                                                                                                    ";
		sql+="	select distinct U1.* from (                                                                                                                                                                ";
		//sql+="	--查询基础开始时间未执行完的合同                                                                                                                                                  ";
		sql +="	SELECT V1.*,Convert(decimal(18,3),isnull(v3.FQTY_Contract,0)) as BYQDL,Convert(decimal(18,3),ISNULL(V2.FQTY_Current,0)) AS SYQJL,Convert(decimal(18,3),isnull(V4.FQTY_STOCKOUT,0)) AS BYFCL, ";
		sql +="					Convert(decimal(18,3),case when ISNULL(V2.FQTY_Current,0)=0 THEN ROUND(ISNULL(V1.HTL,0)-isnull(V4.FQTY_STOCKOUT,0),3) ELSE ";
		sql +="							ROUND(ISNULL(V2.FQTY_Current,0)-isnull(V4.FQTY_STOCKOUT,0),3) END )  BYQJL FROM (  ";
		sql +="		SELECT t4.fname as fCustName,T1.FContractID,T1.FEntryID,t1.FContractNo as FContractNo,Convert(varchar(25),t1.fdate) as FDate,T1.FProductID,  ";
		sql +="			   T3.FName,T3.FModel,T3.F_101 AS fGrade,T3.F_102 AS fHard,t1.FTaxPrice,  ";
		sql +="			   Convert(decimal(18,3),T1.FQTY_Contract) as HTL  ";
		sql+="		   FROM (                                                                                                                                                                     ";
		sql+="	select T1.FContractID,t1.FCUSTOMER,t1.fdate,T2.FEntryID,t1.FContractNo,t2.FProductID,SUM(T2.FQuantity) AS FQTY_Contract,T2.FTaxPrice                                              ";
		sql+="							 from RemoteServer.AIS2009.dbo.t_rpContract t1 Inner Join RemoteServer.AIS2009.dbo.t_rpContractEntry t2 on t1.FContractID=t2.FContractID                  ";
		sql+="							 WHERE T1.FDate>=@RQ1 and t1.FDate<@RQ2+1                                                                                                                 ";
		sql+="							                                                                                                                                                          ";
		sql+="							GROUP BY T1.FContractID,T1.FCUSTOMER,t1.fdate,T2.FEntryID,T1.FContractNo,T2.FProductID,T2.FTaxPrice                                                       ";
		sql+="					) T1 LEFT JOIN (                                                                                                                                                  ";
		sql+="                                                                                                                                                                                    ";
		sql+="	SELECT T2.FContractInterID,T2.FContractEntryID,FITEMID,SUM(FQTY)/1000 AS FQTY_STOCKOUT                                                                                            ";
		sql+="							FROM RemoteServer.AIS2009.dbo.ICStockBill T1 INNER JOIN RemoteServer.AIS2009.dbo.ICStockBillEntry T2                                                      ";
		sql+="							ON T1.FInterID=T2.FInterID                                                                                                                                ";
		sql+="							WHERE T1.FTranType=21 and  T1.FDate>=@RQ1 and t1.FDate<@RQ2+1                                                                                             ";
		sql+="							GROUP BY FContractInterID,FContractEntryID,FItemID                                                                                                        ";
		sql+="									) T2 ON T1.FContractID=T2.FContractInterID AND T1.FEntryID=T2.FContractEntryID                                                                    ";
		sql+="										 LEFT JOIN RemoteServer.AIS2009.dbo.t_ICItem  T3 ON T1.FProductID=T3.FItemID                                                                  ";
		sql+="										 LEFT JOIN RemoteServer.AIS2009.dbo.t_Organization t4 on t1.FCUSTOMER=t4.FItemID                                                              ";
		sql+="										 WHERE Round(ISNULL(T1.FQTY_Contract,0)-ISNULL(FQTY_STOCKOUT,0),3)>0                                                                          ";
		sql+="								) V1 LEFT JOIN                                                                                                                                        ";
		sql+="							                                                                                                                                                          ";
		sql+="									                                                                                                                                                  ";
		sql+="											                                                                                                                                          ";
		sql+="                                                                                                                                                                                    ";
		//sql+="	--上月的合同(应该是非本月合同）                                                                                                                                                   ";
		sql+="							(                                                                                                                                                         ";
		sql+="	SELECT t4.fname as fCustName,T1.FContractID,T1.FEntryID,t1.FContractNo,T1.FProductID,                                                                                             ";
		sql+="		   T3.FName,T3.FModel,T3.F_101 AS fGrade,T3.F_102 AS fHard,t1.FTaxPrice,                                                                                                      ";
		sql+="		   T1.FQTY_Contract ,ROUND(ISNULL(T2.FQTY_STOCKOUT,0),3) AS FQTY_STOCKOUT,                                                                                                    ";
		sql+="		   Case when Round(ISNULL(T1.FQTY_Contract,0)-ISNULL(FQTY_STOCKOUT,0),3)<0 then 0 else                                                                                        ";
		sql+="					 Round(ISNULL(T1.FQTY_Contract,0)-ISNULL(FQTY_STOCKOUT,0),3) end AS FQTY_Current                                                                                  ";
		sql+="		   FROM (                                                                                                                                                                     ";
		sql+="	select T1.FContractID,t1.FCUSTOMER,T2.FEntryID,t1.FContractNo,t2.FProductID,SUM(T2.FQuantity) AS FQTY_Contract,T2.FTaxPrice                                                       ";
		sql+="							 from RemoteServer.AIS2009.dbo.t_rpContract t1 Inner Join RemoteServer.AIS2009.dbo.t_rpContractEntry t2 on t1.FContractID=t2.FContractID                  ";
		sql+="							 WHERE  t1.FDate<@RQDAY1_L2+1                                                                                                                             ";
		sql+="							                                                                                                                                                          ";
		sql+="							GROUP BY T1.FContractID,T1.FCUSTOMER,T2.FEntryID,T1.FContractNo,T2.FProductID,T2.FTaxPrice                                                                ";
		sql+="					) T1 LEFT JOIN (                                                                                                                                                  ";
		sql+="                                                                                                                                                                                    ";
		sql+="	SELECT T2.FContractInterID,T2.FContractEntryID,FITEMID,SUM(FQTY)/1000 AS FQTY_STOCKOUT                                                                                            ";
		sql+="							FROM RemoteServer.AIS2009.dbo.ICStockBill T1 INNER JOIN RemoteServer.AIS2009.dbo.ICStockBillEntry T2                                                      ";
		sql+="							ON T1.FInterID=T2.FInterID                                                                                                                                ";
		sql+="							WHERE T1.FTranType=21 and   t1.FDate<@RQDAY1_L2+1                                                                                                         ";
		sql+="							GROUP BY FContractInterID,FContractEntryID,FItemID                                                                                                        ";
		sql+="									) T2 ON T1.FContractID=T2.FContractInterID AND T1.FEntryID=T2.FContractEntryID                                                                    ";
		sql+="										 LEFT JOIN RemoteServer.AIS2009.dbo.t_ICItem  T3 ON T1.FProductID=T3.FItemID                                                                  ";
		sql+="										 LEFT JOIN RemoteServer.AIS2009.dbo.t_Organization t4 on t1.FCUSTOMER=t4.FItemID 									                          ";
		sql+="											                                                                                                                                          ";
		sql+="								) V2 ON V1.FContractID=V2.FContractID AND V1.FEntryID=V2.FEntryID		                                                                              ";
		sql+="											                                                                                                                                          ";
		sql+="						                                                                                                                                                              ";
		sql+="						                                                                                                                                                              ";
		sql+="								LEFT JOIN                                                                                                                                             ";
		sql+="                                                                                                                                                                                    ";
		//sql+="	--本月的合同                                                                                                                                                                      ";
		sql+="				(select T1.FContractID,t1.FCUSTOMER,T2.FEntryID,t1.FContractNo,t2.FProductID,SUM(T2.FQuantity) AS FQTY_Contract,T2.FTaxPrice                                          ";
		sql+="							 from RemoteServer.AIS2009.dbo.t_rpContract t1 Inner Join RemoteServer.AIS2009.dbo.t_rpContractEntry t2 on t1.FContractID=t2.FContractID                  ";
		sql+="							 WHERE T1.FDate>=@RQDAY1_M and t1.FDate<@RQ2+1                                                                                                            ";
		sql+="							                                                                                                                                                          ";
		sql+="							GROUP BY T1.FContractID,T1.FCUSTOMER,T2.FEntryID,T1.FContractNo,T2.FProductID,T2.FTaxPrice								                                  ";
		sql+="											                                                                                                                                          ";
		sql+="							) V3                                                                                                                                                      ";
		sql+="									 ON V1.FContractID=V3.FContractID AND V1.FEntryID=V3.FEntryID                                                                                     ";
		sql+="									                                                                                                                                                  ";
		sql+="								LEFT JOIN                                                                                                                                             ";
		sql+="					                                                                                                                                                                  ";
		sql+="				(SELECT T2.FContractInterID AS FContractID,T2.FContractEntryID AS FEntryID ,FITEMID,Convert(decimal(18,3),SUM(FQTY)/1000) AS FQTY_STOCKOUT                            ";
		sql+="							FROM RemoteServer.AIS2009.dbo.ICStockBill T1 INNER JOIN RemoteServer.AIS2009.dbo.ICStockBillEntry T2                                                      ";
		sql+="							ON T1.FInterID=T2.FInterID                                                                                                                                ";
		sql+="							WHERE T1.FTranType=21 and  T1.FDate>=@RQDAY1_M and t1.FDate<@RQ2+1                                                                                        ";
		sql+="							GROUP BY FContractInterID,FContractEntryID,FItemID                                                                                                        ";
		sql+="									) V4 ON V1.FContractID=V4.FContractID AND V1.FEntryID=V4.FEntryID                                                                                 ";
		sql+="	)U1 left join RemoteServer.AIS2009.dbo.SEOrderEntry U2 on U2.FItemID  = U1.FProductID  and U1.FContractID = U2.FSourceInterId      and U1.HTL = U2.FAuxQty                                                     ";
		sql+="	 where  U2.FMRPClosed != 0 	AND U2.FMRPAutoClosed != 0                                                                                                                                                   ";
		sql+="	 and len(U1.fcustName) > 0					                                                                                                                                      ";
		
		
		if (StringUtil.isNotEmpty(FName)) {
			sql += " and U1.FName like " + "'%" + FName + "%'";
		}
		if (StringUtil.isNotEmpty(FCustName)) {
			sql += " and U1.FCustName like " + "'%" + FCustName + "%'";
		}

		if (StringUtil.isNotEmpty(FContractNo)) {
			sql += " and U1.FContractNo like " + "'%" + FContractNo + "%'";
		}
		
		sql += " order by U1.FDate ";
	//	List<Map> list = saleMiniDao.execContract(sql);

	//	List<Map<String, Object>> maplist = null;
    	List<Map> maplist = null;

		try {
		   maplist = saleMiniDao.execContract(sql);

			//maplist = systemService.findForJdbc(sql);
			List<String[]> list = new ArrayList<String[]>();

			for (Map<String, Object> map : maplist) {
				String[] arr = new String[map.size()];
				arr[0] = (String) map.get("FCustName");
				arr[1] = (String) map.get("FDate");
				arr[2] = (String) map.get("FContractNo");
				arr[3] = (String) map.get("FName");
				arr[4] =  ((String) map.get("FModel"));
				arr[5] = (String) map.get("fHard");
				arr[6] = String.valueOf((Double) map.get("FTaxPrice"));
				arr[7] = String.valueOf((BigDecimal) map.get("HTL"));
				arr[8] = String.valueOf((BigDecimal) map.get("BYQDL"));
				arr[9] = String.valueOf((BigDecimal) map.get("SYQJL"));
				arr[10] = String.valueOf((BigDecimal) map.get("BYFCL"));
				arr[11] = String.valueOf((BigDecimal) map.get("BYQJL"));
				list.add(arr);

			}

			String[] columnNames = new String[] { "购货单位", "合同日期", "合同号", "牌号", "规格", "硬度", "含税单价", "合同量", "本月签订量",
					"上月欠交量", "本月发出量", "本月欠交量" };
			String titleName = "合同执行情况.xls";

			OutputStream fOut = response.getOutputStream();
			response.setHeader("Content-Disposition", "attachment; filename="
					+ new String(titleName.getBytes("GB2312"), "ISO8859-1"));

			ExcelUtils.create(request, list, columnNames, titleName, fOut);

		} catch (Exception ex) {

		}

	}

	/**
	 * easyui AJAX请求数据 销售欠款分析
	 * 
	 * @param request
	 * @param response
	 * @param dataGrid
	 * @param user
	 */

	@RequestMapping(params = "datagrid2")
	public void datagrid2(HttpServletRequest request, HttpServletResponse response, DataGrid dataGrid) {


		String FDate = request.getParameter("FDate");
		String FCUSTNAME = request.getParameter("FCUSTNAME");

		
String sql ="		DECLARE @RQ SMALLDATETIME                                                                                                                                  ";                                                                                                         
sql+="		DECLARE @RQDAY1_Y SMALLDATETIME                                                                                                                            ";
sql+="		DECLARE @RQDAY1_M SMALLDATETIME                                                                                                                            ";
sql+="		DECLARE @RQDAY2_M SMALLDATETIME                                                                                                                            ";
sql+="		DECLARE @YEAR INT                                                                                                                                          ";
sql+="		DECLARE @MONTH INT                                                                                                                                         ";
sql+="																																				                   ";
//sql+="		SET @RQ='2015-12-01'                                                                                                                                       ";
sql+="	SET @RQ='" + FDate + "-01'                                                                                                                                   ";
sql+="		SET @YEAR=YEAR(@RQ)                                                                                                                                        ";
sql+="		SET @MONTH=MONTH(@RQ)                                                                                                                                      ";
sql+="																																				                   ";
//sql+="		--本月第一天                                                                                                                                               ";
sql+="		SELECT @RQDAY1_M=  CONVERT(CHAR(10),DATEADD(dd,-DAY(@RQ)+1,@RQ),111)                                                                                       ";
//sql+="		--本月最后一天                                                                                                                                             ";
sql+="		SELECT @RQDAY2_M=  CONVERT(CHAR(10),DATEADD(ms,-3,DATEADD(mm,DATEDIFF(m,0,@RQ)+1,0)),111)                                                                  ";
sql+="																																				                   ";
sql+="		select *,(SKZS-CDSK-XKSK-DHSK) QTSK,(SQQK + KPJE - SKZS) YMQK  from (                                                                                      ";           
//sql+="		-- 上期欠款数（本期期初余额）                                                                                                                              ";
sql+="		select T6.FName FCustName,T1.FCustomer,isnull(T1.sum_FBeginBalanceFor,0) SQQK,isnull(T2.sum_qty,0) KPSL,                                                   ";
sql+="		isnull(T2.sum_amount,0) KPJE,isnull(T3.fAmount,0) XKSK,isnull(T4.fAmount,0) CDSK,isnull(T7.fAmount,0) DHSK,isnull(T5.fAmount,0) SKZS                       ";          
sql+="		 from (                                                                                                                                                    ";
sql+="		select t1.FCustomer,sum(t1.FBeginBalanceFor) sum_FBeginBalanceFor from RemoteServer.AIS2009.dbo.t_RP_ContactBal t1                                         ";
sql+="		LEFT JOIN RemoteServer.AIS2009.dbo.t_Organization t2 ON t1.FCustomer=t2.FItemID 	                                                                       ";
sql+="		where 1=1                                                                                                                                                  ";
sql+="		and t1.FYear = @YEAR                                                                                                                                       ";
sql+="		AND t1.FPeriod = @MONTH                                                                                                                                    ";
sql+="		group by t1.FCustomer                                                                                                                                      ";
sql+="		) T1                                                                                                                                                       ";
sql+="																																				                   ";
sql+="		LEFT JOIN                                                                                                                                                  ";
sql+="																																				                   ";
sql+="		(                                                                                                                                                          ";
//sql+="		-- 开票数量 开票金额                                                                                                                                       ";
sql+="		 SELECT ICSale.FCustID,Convert(decimal(18,4),sum(isnull(ICSaleEntry.FAuxQty,0.0000))) sum_qty,                                                             ";
sql+="		 CONVERT(decimal(18,4),sum(isnull(ICSaleEntry.FAmountincludetax,0.0000))) sum_amount                                                                                              ";
sql+="		FROM  RemoteServer.AIS2009.dbo.ICSale  INNER JOIN RemoteServer.AIS2009.dbo.ICSaleEntry  ON ICSale.FInterID=ICSaleEntry.FInterID                            ";
sql+="		 LEFT  JOIN RemoteServer.AIS2009.dbo.t_ICItem t_ICItem1 ON ICSaleEntry.FItemID=t_ICItem1.FItemID AND t_ICItem1.FItemID<>0                                  ";
sql+="		 LEFT  JOIN RemoteServer.AIS2009.dbo.t_Item t_Item ON ICSale.FCustID=t_Item.FItemID AND t_Item.FItemID<>0                                                  ";
sql+="		 WHERE 1=1                                                                                                                                                 ";
sql+="		 AND ICSale.FDate < @RQDAY2_M+1                                                                                                                            ";
sql+="		 AND ICSale.FDate >=  @RQDAY1_M                                                                                                                            ";
sql+="		 AND isnull(ICSale.FSaleStyle,0)=101 AND isnull(ICSale.FCheckerID,0)>0 AND ICSale.FClassTypeID=1000002                                                     ";
sql+="		 AND ( ICSale.FSubSystemID>=0 and ICSale.finterid in(select finvoiceid from RemoteServer.AIS2009.dbo.t_rp_contact                                          ";
sql+="		 where ftype=3 and finvoiceid>0) and isnull(ICSale.FCancellation,0)=0 )                                                                                    ";
sql+="		 group by ICSale.FCustID                                                                                                                                   ";
sql+="		 ) T2 ON T1.FCustomer = T2.FCustID                                                                                                                         ";
sql+="																																				                   ";
sql+="		 LEFT JOIN                                                                                                                                                 ";
sql+="		 (                                                                                                                                                         ";
//sql+="		-- 现款收款                                                                                                                                                ";
sql+="		 SELECT FCustomer,sum(FAmount) as fAmount  FROM RemoteServer.AIS2009.dbo.t_RP_NewReceiveBill                                                               ";
sql+="		 WHERE ISNULL(FChecker,0)>0 AND FDate>=@RQDAY1_M and FDate<@RQDAY2_M+1                                                                                     ";
sql+="		 AND ISNULL(FSettle,0)=1                                                                                                                                   ";
sql+="		 Group by FCustomer                                                                                                                                        ";
sql+="		 ) T3 ON T1.FCustomer=T3.FCustomer                                                                                                                         ";
sql+="                                                                                                                                                                 ";
sql+="                                                                                                                                                                 ";
sql+="		 LEFT JOIN                                                                                                                                                 ";
sql+="		 (                                                                                                                                                         ";
//sql+="		--  电汇                                                                                                                                                   ";
sql+="		 SELECT FCustomer,sum(FAmount) as fAmount  FROM RemoteServer.AIS2009.dbo.t_RP_NewReceiveBill                                                               ";
sql+="		 WHERE ISNULL(FChecker,0)>0 AND FDate>=@RQDAY1_M and FDate<@RQDAY2_M+1                                                                                     ";
sql+="		 AND ISNULL(FSettle,0)=3                                                                                                                                   ";
sql+="		 Group by FCustomer                                                                                                                                        ";
sql+="		 ) T7 ON T1.FCustomer=T7.FCustomer                                                                                                                         ";
sql+="                                                                                                                                                                 ";
sql+="																																				                   ";
sql+="		LEFT JOIN                                                                                                                                                  ";
sql+="		(                                                                                                                                                          ";
//sql+="	-- 承兑收款                                                                                                                                                    ";
sql+="		 SELECT FCustomer,sum(FAmount) as fAmount  FROM RemoteServer.AIS2009.dbo.t_RP_NewReceiveBill                                                               ";
sql+="		 WHERE ISNULL(FChecker,0)>0 AND FDate>=@RQDAY1_M and FDate<@RQDAY2_M+1                                                                                     ";
sql+="		 AND ISNULL(FSettle,0) in(2,4)                                                                                                                             ";
sql+="		 Group by FCustomer                                                                                                                                        ";
sql+="		 ) T4 ON T1.FCustomer=T4.FCustomer                                                                                                                         ";
sql+="																																				                   ";
sql+="		LEFT JOIN                                                                                                                                                  ";
sql+="		(                                                                                                                                                          ";
//sql+="		-- 收款总数                                                                                                                                                ";                                                                       
sql+="		 SELECT FCustomer,sum(FAmount) as fAmount  FROM RemoteServer.AIS2009.dbo.t_RP_NewReceiveBill                                                               ";
sql+="		 WHERE ISNULL(FChecker,0)>0 AND FDate>=@RQDAY1_M and FDate<@RQDAY2_M+1                                                                                     ";                                                                                                                   
sql+="		 Group by FCustomer                                                                                                                                        ";
sql+="		 ) T5 ON T1.FCustomer=T5.FCustomer                                                                                                                         ";
sql+="																																				                   ";
sql+="		 LEFT JOIN RemoteServer.AIS2009.dbo.t_Organization T6 ON T1.FCustomer=T6.FItemID                                                                           ";
sql+="																																				                   ";
sql+="		) T8  where 1=1                                                                                                                                            ";

		 

      
		


			if (StringUtil.isNotEmpty(FCUSTNAME)) {
				sql += " and FCustName like " + "'%" + FCUSTNAME + "%'";
			}		
		List<Map> list = saleMiniDao.execContract2(sql);
		
		
		
		   // 数量合计
			BigDecimal sum_qty = new BigDecimal("0.0000");
			BigDecimal sum_qty2 = new BigDecimal("0.0000");
			BigDecimal sum_qty3 = new BigDecimal("0.0000");
			BigDecimal sum_qty4 = new BigDecimal("0.0000");
			BigDecimal sum_qty5 = new BigDecimal("0.0000");
			BigDecimal sum_qty6 = new BigDecimal("0.0000");
			BigDecimal sum_qty7 = new BigDecimal("0.0000");
			BigDecimal sum_qty8 = new BigDecimal("0.0000");
			BigDecimal sum_qty9 = new BigDecimal("0.0000");
			
			
			for (Map<String, Object> map : list)
			{
			   sum_qty = sum_qty.add((BigDecimal) map.get("SQQK"));
			   sum_qty2 = sum_qty2.add((BigDecimal) map.get("KPSL"));
			   sum_qty3 = sum_qty3.add((BigDecimal) map.get("KPJE"));
			   sum_qty4 = sum_qty4.add((BigDecimal) map.get("XKSK"));
			   sum_qty5 = sum_qty5.add((BigDecimal) map.get("CDSK"));
			   sum_qty6 = sum_qty6.add((BigDecimal) map.get("SKZS"));
			   sum_qty7 = sum_qty7.add((BigDecimal) map.get("YMQK"));
			   sum_qty8 = sum_qty8.add((BigDecimal) map.get("DHSK"));
			   sum_qty9 = sum_qty9.add((BigDecimal) map.get("QTSK"));
			}

		// List<JeecgMinidaoEntity> list =
		// jeecgMinidaoService.listAll(jeecgMinidao, dataGrid.getPage(),
		// dataGrid.getRows());

		dataGrid.setTotal(list.size());
		dataGrid.setResults(list);
		
		dataGrid.setFooter("FCUSTNAME:合计" + ",SQQK:" + sum_qty+ ",KPSL:" + sum_qty2+ ",KPJE:" + sum_qty3+ ",XKSK:" + sum_qty4+ ",CDSK:"
		+ sum_qty5+ ",DHSK:"  + sum_qty8+ ",QTSK:"  + sum_qty9 + ",SKZS:" + sum_qty6+ ",YMQK:" + sum_qty7);
		TagUtil.datagrid(response, dataGrid);
	}
   
	
	/**
	 * 
	 * 導出  欠款分析
	 * 
	 * @param request
	 * @param response
	 * @param dataGrid
	 */
	@RequestMapping(params = "export_qkfx")
	public void export_qkfx(HttpServletRequest request, HttpServletResponse response, DataGrid dataGrid) {

		
		String FDate = request.getParameter("FDate");
		String FCUSTNAME = request.getParameter("FCUSTNAME");

	String	sql ="                                                                                                                                                 ";
		sql+="		DECLARE @RQ SMALLDATETIME                                                                                                                  ";
		sql+="		DECLARE @RQDAY1_Y SMALLDATETIME                                                                                                            ";
		sql+="		DECLARE @RQDAY1_M SMALLDATETIME                                                                                                            ";
		sql+="		DECLARE @RQDAY2_M SMALLDATETIME                                                                                                            ";
		sql+="		DECLARE @YEAR INT                                                                                                                          ";
		sql+="		DECLARE @MONTH INT                                                                                                                         ";
		sql+="                                                                                                                                                 ";
		//--查询账期   
		//sql+="		SET @RQ='2015-11-30'                                                                                                          ";
		sql += " SET @RQ='" + FDate + "-01'";
		sql+="		SET @YEAR=YEAR(@RQ)                                                                                                                        ";
		sql+="		SET @MONTH=MONTH(@RQ)                                                                                                                      ";
		sql+="                                                                                                                                                 ";
		//		--本月第一天                                                                                                                           
		sql+="		SELECT @RQDAY1_M=  CONVERT(CHAR(10),DATEADD(dd,-DAY(@RQ)+1,@RQ),111)                                                                       ";
		//		--本月最后一天                                                                                                                             ";
		sql+="		SELECT @RQDAY2_M=  CONVERT(CHAR(10),DATEADD(ms,-3,DATEADD(mm,DATEDIFF(m,0,@RQ)+1,0)),111)                                                  ";
		sql+="                                                                                                                                                 ";
		sql+="		select *,(SQQK + KPJE - SKZS) YMQK  from (                                                                                                 ";
		//		-- 上期欠款数（本期期初余额）                                                                                                              ";
		sql+="		select T6.FName FCustName,T1.FCustomer,isnull(T1.sum_FBeginBalanceFor,0) SQQK,isnull(T2.sum_qty,0) KPSL,                                   ";
		sql+="		isnull(T2.sum_amount,0) KPJE,isnull(T3.fAmount,0) XKSK,isnull(T4.fAmount,0) CDSK,isnull(T7.fAmount,0) DHSK,isnull(T5.fAmount,0) SKZS                                 ";
		sql+="		 from (                                                                                                                                    ";
		sql+="		select t1.FCustomer,sum(t1.FBeginBalanceFor) sum_FBeginBalanceFor from RemoteServer.AIS2009.dbo.t_RP_ContactBal t1                         ";
		sql+="		LEFT JOIN RemoteServer.AIS2009.dbo.t_Organization t2 ON t1.FCustomer=t2.FItemID 	                                                       ";
		sql+="		where 1=1                                                                                                                                  ";
		sql+="		and t1.FYear = @YEAR                                                                                                                       ";
		sql+="		AND t1.FPeriod = @MONTH                                                                                                                    ";
		sql+="		group by t1.FCustomer                                                                                                                      ";
		sql+="		) T1                                                                                                                                       ";
		sql+="                                                                                                                                                 ";
		sql+="		LEFT JOIN                                                                                                                                  ";
		sql+="                                                                                                                                                 ";
		sql+="		(                                                                                                                                          ";
		//		-- 开票数量 开票金额                                                                                                                       ";
		sql+="		 SELECT ICSale.FCustID,Convert(decimal(18,4),sum(isnull(ICSaleEntry.FAuxQty,0.0000))) sum_qty,                                             ";
		sql+="		 sum(isnull(ICSaleEntry.FAmount,0.0000)) sum_amount                                                                                        ";
		sql+="		FROM  RemoteServer.AIS2009.dbo.ICSale  INNER JOIN RemoteServer.AIS2009.dbo.ICSaleEntry  ON ICSale.FInterID=ICSaleEntry.FInterID            ";
		sql+="		 LEFT  JOIN RemoteServer.AIS2009.dbo.t_ICItem t_ICItem1 ON ICSaleEntry.FItemID=t_ICItem1.FItemID AND t_ICItem1.FItemID<>0                  ";
		sql+="		 LEFT  JOIN RemoteServer.AIS2009.dbo.t_Item t_Item ON ICSale.FCustID=t_Item.FItemID AND t_Item.FItemID<>0                                  ";
		sql+="		 WHERE 1=1                                                                                                                                 ";
		sql+="		 AND ICSale.FDate < @RQDAY2_M+1                                                                                                            ";
		sql+="		 AND ICSale.FDate >=  @RQDAY1_M                                                                                                            ";
		sql+="		 AND isnull(ICSale.FSaleStyle,0)=101 AND isnull(ICSale.FCheckerID,0)>0 AND ICSale.FClassTypeID=1000002                                     ";
		sql+="		 AND ( ICSale.FSubSystemID>=0 and ICSale.finterid in(select finvoiceid from RemoteServer.AIS2009.dbo.t_rp_contact                          ";
		sql+="		 where ftype=3 and finvoiceid>0) and isnull(ICSale.FCancellation,0)=0 )                                                                    ";
		sql+="		 group by ICSale.FCustID                                                                                                                   ";
		sql+="		 ) T2 ON T1.FCustomer = T2.FCustID                                                                                                         ";
		sql+="		                                                                                                                                           ";
		sql+="		 LEFT JOIN                                                                                                                                 ";
		sql+="		 (                                                                                                                                         ";
		//		-- 现款收款                                                                                                                                ";
		sql+="		 SELECT FCustomer,sum(FAmount) as fAmount  FROM RemoteServer.AIS2009.dbo.t_RP_NewReceiveBill                                               ";
		sql+="		 WHERE ISNULL(FChecker,0)>0 AND FDate>=@RQDAY1_M and FDate<@RQDAY2_M+1                                                                     ";
		sql+="		 AND ISNULL(FSettle,0)=1                                                                                                                   ";
		sql+="		 Group by FCustomer                                                                                                                        ";
		sql+="		 ) T3 ON T1.FCustomer=T3.FCustomer                                                                                                         ";
		
		
		sql+="		 LEFT JOIN                                                                                                                                 ";
		sql+="		 (                                                                                                                                         ";
		//		--  电汇                                                                                                                               ";
		sql+="		 SELECT FCustomer,sum(FAmount) as fAmount  FROM RemoteServer.AIS2009.dbo.t_RP_NewReceiveBill                                               ";
		sql+="		 WHERE ISNULL(FChecker,0)>0 AND FDate>=@RQDAY1_M and FDate<@RQDAY2_M+1                                                                     ";
		sql+="		 AND ISNULL(FSettle,0)=3                                                                                                                  ";
		sql+="		 Group by FCustomer                                                                                                                        ";
		sql+="		 ) T7 ON T1.FCustomer=T7.FCustomer                                                                                                         ";
		
		sql+="                                                                                                                                                 ";
		sql+="		LEFT JOIN                                                                                                                                  ";
		sql+="		(                                                                                                                                          ";
		//	-- 承兑收款                                                                                                                                ";
		sql+="		 SELECT FCustomer,sum(FAmount) as fAmount  FROM RemoteServer.AIS2009.dbo.t_RP_NewReceiveBill                                               ";
		sql+="		 WHERE ISNULL(FChecker,0)>0 AND FDate>=@RQDAY1_M and FDate<@RQDAY2_M+1                                                                     ";
		sql+="		 AND ISNULL(FSettle,0)=2                                                                                                                   ";
		sql+="		 Group by FCustomer                                                                                                                        ";
		sql+="		 ) T4 ON T1.FCustomer=T4.FCustomer                                                                                                         ";
		sql+="                                                                                                                                                 ";
		sql+="		LEFT JOIN                                                                                                                                  ";
		sql+="		(                                                                                                                                          ";
		//		-- 收款总数                                                                                                                                ";
		sql+="		select FCustomer,sum(famount) as Famount from 	(                                                                                          ";
		sql+="		 SELECT FCustomer,FAmount as fAmount  FROM RemoteServer.AIS2009.dbo.t_RP_NewReceiveBill                                                    ";
		sql+="		 WHERE ISNULL(FChecker,0)>0 AND FDate>=@RQDAY1_M and FDate<@RQDAY2_M+1                                                                     ";
		sql+="		 UNION ALL 		                                                                                                                           ";
		sql+="		SELECT 	T1.FCustID FCustomer,FAmount                                                                                                       ";
			sql+="		FROM RemoteServer.AIS2009.dbo.ICSale T1 INNER JOIN RemoteServer.AIS2009.dbo.ICSaleEntry T2 ON T1.FInterID=T2.FInterID 					   ";
			sql+="		WHERE ISNULL(t1.FCheckerID,0)>0 AND FDate>=@RQDAY1_M and FDate<@RQDAY2_M+1                                                                 ";
			sql+="		   and t1.FSaleStyle=100                                                                                                                   ";
		sql+="		   )tt                                                                                                                                     ";
		sql+="		 Group by FCustomer                                                                                                                        ";
		sql+="		 ) T5 ON T1.FCustomer=T5.FCustomer                                                                                                         ";
		sql+="		                                                                                                                                           ";
		sql+="		 LEFT JOIN RemoteServer.AIS2009.dbo.t_Organization T6 ON T1.FCustomer=T6.FItemID                                                           ";
		sql+="                                                                                                                                                 ";
		sql+="		) T8  where 1=1                                                                                                                                      ";
      
		


			if (StringUtil.isNotEmpty(FCUSTNAME)) {
				sql += " and FCustName like " + "'%" + FCUSTNAME + "%'";
			}			
		  //List<Map> list = saleMiniDao.execContract2(sql);
		
		
		
	
			List<Map> maplist = null;

		try {
			maplist = saleMiniDao.execContract2(sql);
			
			List<String[]> list = new ArrayList<String[]>();

			for (Map<String, Object> map : maplist) {
				String[] arr = new String[map.size()];
				arr[0] = (String) map.get("FCUSTNAME");
				arr[1] = String.valueOf((BigDecimal) map.get("SQQK"));
				arr[2] = String.valueOf((BigDecimal) map.get("KPSL"));
				arr[3] = String.valueOf((BigDecimal) map.get("KPJE"));
				arr[4] = String.valueOf((BigDecimal) map.get("XKSK"));
				arr[5] = String.valueOf((BigDecimal) map.get("CDSK"));
				arr[6] = String.valueOf((BigDecimal) map.get("DHSK"));
				arr[7] = String.valueOf((BigDecimal) map.get("SKZS"));
				arr[8] = String.valueOf((BigDecimal) map.get("YMQK"));
				
				list.add(arr);

			}
            
			   // 数量合计
					BigDecimal sum_qty = new BigDecimal("0.0000");
					BigDecimal sum_qty2 = new BigDecimal("0.0000");
					BigDecimal sum_qty3 = new BigDecimal("0.0000");
					BigDecimal sum_qty4 = new BigDecimal("0.0000");
					BigDecimal sum_qty5 = new BigDecimal("0.0000");
					BigDecimal sum_qty6 = new BigDecimal("0.0000");
					BigDecimal sum_qty7 = new BigDecimal("0.0000");
					BigDecimal sum_qty8 = new BigDecimal("0.0000");
					
					for (Map<String, Object> map : maplist)
					{
					   sum_qty = sum_qty.add((BigDecimal) map.get("SQQK"));
					   sum_qty2 = sum_qty2.add((BigDecimal) map.get("KPSL"));
					   sum_qty3 = sum_qty3.add((BigDecimal) map.get("KPJE"));
					   sum_qty4 = sum_qty4.add((BigDecimal) map.get("XKSK"));
					   sum_qty5 = sum_qty5.add((BigDecimal) map.get("CDSK"));
					   sum_qty6 = sum_qty6.add((BigDecimal) map.get("DHSK"));
					   sum_qty7 = sum_qty7.add((BigDecimal) map.get("SKZS"));
					   sum_qty8 = sum_qty8.add((BigDecimal) map.get("YMQK"));
					  
					}
			
		   String[] sums = new String[]{"合计",String.valueOf(sum_qty),String.valueOf(sum_qty2),String.valueOf(sum_qty3),String.valueOf(sum_qty4),String.valueOf(sum_qty5),String.valueOf(sum_qty6),String.valueOf(sum_qty7),String.valueOf(sum_qty8)};
		   list.add(sums);			
		
		   String[] columnNames = new String[] { "销售欠款分析", "上期欠款数", "开票数量", "开票金额", "现款收款", "承兑收款", "电汇","收款总数", "月末欠款数" };
			String titleName = "销售欠款分析.xls";

			OutputStream fOut = response.getOutputStream();
			response.setHeader("Content-Disposition", "attachment; filename="
					+ new String(titleName.getBytes("GB2312"), "ISO8859-1"));

			ExcelUtils.create(request, list, columnNames, titleName, fOut);

		} catch (Exception ex) {

		}

	}
	
	/**
	 * easyui AJAX请求数据 销售汇总
	 * 
	 * @param request
	 * @param response
	 * @param dataGrid
	 * @param user
	 */

	@RequestMapping(params = "datagrid3")
	public void datagrid3(HttpServletRequest request, HttpServletResponse response, DataGrid dataGrid) {

		String FDate= request.getParameter("FDate");
		

	String 	sql = "	DECLARE @FNAME varchar(50)                                                                                                                                          ";
		sql += "	DECLARE @RQ smalldatetime                                                                                                                                           ";
		sql += "	DECLARE @RQ_S smalldatetime                                                                                                                                         ";
		sql += "                                                                                                                                                                        ";
		sql += "	DECLARE @YEAR1 INT ,@MONTH1 INT,@YEAR2 INT,@MONTH2 INT                                                                                                              ";
	    sql += " DECLARE @RQDAY1_L2 SMALLDATETIME ";
		
		//	sql += "	SET @RQ='2015-10-01'                                                                                                                                                ";
		sql += " SET  @RQ='" + FDate + "-01"+ "'";
		sql += "                                                                                                                                                                        ";
		sql += "	SET @YEAR1=YEAR(@RQ)                                                                                                                                                ";
		sql += "	SET @MONTH1=MONTH(@RQ)                                                                                                                                              ";
		sql += "                                                                                                                                                                        ";
	//	sql += "	SET @YEAR2=YEAR(@RQ)-1                                                                                                                                              ";
	//	sql += "	SET @MONTH2=MONTH(@RQ)-1                                                                                                                                            ";
		
		sql += " SELECT @RQDAY1_L2=CONVERT(CHAR(10),DATEADD(ms,-3,DATEADD(mm, DATEDIFF(mm,0,@RQ),0)),111)+' 00:00:00' ";
		sql += " SET @MONTH2=  MONTH(@RQDAY1_L2) ";
		sql += " SET @YEAR2=YEAR(@RQDAY1_L2) ";
		
		sql += "                                                                                                                                                                        ";
		sql += "	SELECT T1.FName FName,Convert(decimal(18,4),T1.sum_qty) FAuxQty,T1.sum_amount FAmount,T1.price FPrice,Convert(decimal(18,4),T2.sum_qty) Y_FAuxQty,                  ";
		sql += "	T2.sum_amount Y_FAmount,Convert(decimal(18,4),isnull(T3.sum_qty,0.0000)) LYM_FAuxQty,Convert(decimal(18,4),(T1.sum_qty-isnull(T3.sum_qty,0.0000))) YADD_FAuxQty,    ";
		sql += "	Convert(decimal(18,4),isnull(T4.sum_qty,0.0000)) LM_FAuxQty,Convert(decimal(18,4),(T1.sum_qty -isnull(T4.sum_qty,0.0000))) MADD_FAuxQty,isnull(T4.price,0.0000) LM_FPrice,         ";
		sql += "	isnull((T1.price - T4.price),0.0000) MADD_PRICE                                                                                                                                    ";
		sql += "	 FROM (                                                                                                                                                             ";
		sql += "	SELECT reverse(FName) FName,sum_amount,sum_qty,price FROM (                                                                                                         ";
		sql += "	SELECT substring(reverse(t.FName),1,4) FName,sum(FAuxQty) sum_qty,sum(FAmount) sum_amount,sum(FAmount)/sum(FAuxQty) as price from (                                 ";
		sql += "	SELECT t_ICItem1.FName,ICSaleEntry.FAuxQty,ICSaleEntry.FAmount                                                                                                      ";
		sql += "	FROM  RemoteServer.AIS2009.dbo.ICSale  INNER JOIN RemoteServer.AIS2009.dbo.ICSaleEntry  ON ICSale.FInterID=ICSaleEntry.FInterID                                     ";
		sql += "	 LEFT  JOIN RemoteServer.AIS2009.dbo.t_ICItem t_ICItem1 ON ICSaleEntry.FItemID=t_ICItem1.FItemID AND t_ICItem1.FItemID<>0                                           ";
		sql += "	 WHERE 1=1                                                                                                                                                          ";
		sql += "	 AND ICSale.FYear =  @YEAR1                                                                                                                                         ";
		sql += "	 AND ICSale.FPeriod =  @MONTH1                                                                                                                                      ";
		sql += "	 AND isnull(ICSale.FSaleStyle,0)=101 AND isnull(ICSale.FCheckerID,0)>0 AND ICSale.FClassTypeID=1000002                                                              ";
		sql += "	 AND ( ICSale.FSubSystemID>=0 and ICSale.finterid in(select finvoiceid from RemoteServer.AIS2009.dbo.t_rp_contact                                                   ";
		sql += "	 where ftype=3 and finvoiceid>0) and isnull(ICSale.FCancellation,0)=0 )                                                                                             ";
		sql += "	 ) t                                                                                                                                                                ";
		sql += "	group by substring(reverse(t.FName),1,4)                                                                                                                            ";
		sql += "	)tt                                                                                                                                                                 ";
		sql += "	) T1                                                                                                                                                                ";
		sql += "                                                                                                                                                                        ";
		sql += "	LEFT JOIN                                                                                                                                                           ";
		sql += "                                                                                                                                                                        ";
		sql += "	(                                                                                                                                                                   ";
		sql += "	SELECT  reverse(FName) FName,sum_qty,sum_amount FROM (                                                                                                              ";
		sql += "	SELECT substring(reverse(t.FName),1,4) FName,sum(FAuxQty) sum_qty,sum(FAmount) sum_amount FROM(                                                                     ";
		sql += "	SELECT t_ICItem1.FName,ICSaleEntry.FAuxQty,ICSaleEntry.FAmount                                                                                                      ";
		sql += "	FROM  RemoteServer.AIS2009.dbo.ICSale  INNER JOIN RemoteServer.AIS2009.dbo.ICSaleEntry  ON ICSale.FInterID=ICSaleEntry.FInterID                                     ";
		sql += "	 LEFT  JOIN RemoteServer.AIS2009.dbo.t_ICItem t_ICItem1 ON ICSaleEntry.FItemID=t_ICItem1.FItemID AND t_ICItem1.FItemID<>0                                           ";
		sql += "	 WHERE  1=1                                                                                                                                                         ";
		sql += "	 AND ICSale.FYear = @YEAR1                                                                                                                                          ";
		sql += " AND ICSale.FPeriod <=  @MONTH1   ";
		sql += "	 AND isnull(ICSale.FSaleStyle,0)=101 AND isnull(ICSale.FCheckerID,0)>0 AND ICSale.FClassTypeID=1000002                                                              ";
		sql += "	 AND ( ICSale.FSubSystemID>=0 and ICSale.finterid in(select finvoiceid from RemoteServer.AIS2009.dbo.t_rp_contact                                                   ";
		sql += "	 where ftype=3 and finvoiceid>0) and isnull(ICSale.FCancellation,0)=0 )                                                                                             ";
		sql += "	 )t                                                                                                                                                                 ";
		sql += "	group by substring(reverse(t.FName),1,4)                                                                                                                            ";
		sql += "	) tt                                                                                                                                                                ";
		sql += "	) T2 ON T1.FName = T2.FName                                                                                                                                         ";
		sql += "                                                                                                                                                                        ";
		sql += "	LEFT JOIN                                                                                                                                                           ";
		sql += "                                                                                                                                                                        ";
		sql += "	(                                                                                                                                                                   ";
		sql += "	SELECT  reverse(FName) FName,sum_qty FROM (                                                                                                                         ";
		sql += "	SELECT substring(reverse(t.FName),1,4) FName,sum(FAuxQty) sum_qty from (                                                                                            ";
		sql += "	SELECT t_ICItem1.FName,ICSaleEntry.FAuxQty                                                                                                                          ";
		sql += "	FROM  RemoteServer.AIS2009.dbo.ICSale  INNER JOIN RemoteServer.AIS2009.dbo.ICSaleEntry  ON ICSale.FInterID=ICSaleEntry.FInterID                                     ";
		sql += "	 LEFT  JOIN RemoteServer.AIS2009.dbo.t_ICItem t_ICItem1 ON ICSaleEntry.FItemID=t_ICItem1.FItemID AND t_ICItem1.FItemID<>0                                           ";
		sql += "	 WHERE  1=1                                                                                                                                                         ";
		sql += "	 AND ICSale.FYear =  @YEAR2                                                                                                                                         ";
		sql += "	 AND ICSale.FPeriod =  @MONTH1                                                                                                                                      ";
		sql += "	 AND isnull(ICSale.FSaleStyle,0)=101 AND isnull(ICSale.FCheckerID,0)>0 AND ICSale.FClassTypeID=1000002                                                              ";
		sql += "	 AND ( ICSale.FSubSystemID>=0 and ICSale.finterid in(select finvoiceid from RemoteServer.AIS2009.dbo.t_rp_contact                                                   ";
		sql += "	 where ftype=3 and finvoiceid>0) and isnull(ICSale.FCancellation,0)=0 )                                                                                             ";
		sql += "	 ) t                                                                                                                                                                ";
		sql += "	group by substring(reverse(t.FName),1,4)                                                                                                                            ";
		sql += "	)tt                                                                                                                                                                 ";
		sql += "	) T3 ON T1.FName = T3.FName                                                                                                                                         ";
		sql += "                                                                                                                                                                        ";
		sql += "	LEFT JOIN                                                                                                                                                           ";
		sql += "                                                                                                                                                                        ";
		sql += "	(                                                                                                                                                                   ";
		sql += "	SELECT  reverse(FName) FName,sum_qty,price FROM (                                                                                                                   ";
		sql += "	SELECT substring(reverse(t.FName),1,4) FName,sum(FAuxQty) sum_qty,sum(FAmount)/sum(FAuxQty) as price from (                                                         ";
		sql += "	select t_ICItem1.FName,ICSaleEntry.FAuxQty,ICSaleEntry.FAmount                                                                                                      ";
		sql += "	FROM  RemoteServer.AIS2009.dbo.ICSale  INNER JOIN RemoteServer.AIS2009.dbo.ICSaleEntry  ON ICSale.FInterID=ICSaleEntry.FInterID                                     ";
		sql += "	 LEFT  JOIN RemoteServer.AIS2009.dbo.t_ICItem t_ICItem1 ON ICSaleEntry.FItemID=t_ICItem1.FItemID AND t_ICItem1.FItemID<>0                                           ";
		sql += "	 WHERE 1=1                                                                                                                                                          ";
		sql += "	 AND ICSale.FYear =  @YEAR2                                                                                                                                         ";
		sql += "	 AND ICSale.FPeriod =  @MONTH2                                                                                                                                      ";
		sql += "	 AND isnull(ICSale.FSaleStyle,0)=101 AND isnull(ICSale.FCheckerID,0)>0 AND ICSale.FClassTypeID=1000002                                                              ";
		sql += "	 AND ( ICSale.FSubSystemID>=0 and ICSale.finterid in(select finvoiceid from RemoteServer.AIS2009.dbo.t_rp_contact                                                   ";
		sql += "	 where ftype=3 and finvoiceid>0) and isnull(ICSale.FCancellation,0)=0 )                                                                                             ";
		sql += "	 ) t                                                                                                                                                                ";
		sql += "	group by substring(reverse(t.FName),1,4)                                                                                                                            ";
		sql += "	)tt                                                                                                                                                                 ";
		sql += "	) T4 ON T1.FName = T4.FName                                                                                                                                         ";



		List<Map> list = saleMiniDao.execContract3(sql);
        
		
		  // 数量合计
				BigDecimal sum_qty = new BigDecimal("0.0000");
				BigDecimal sum_qty2 = new BigDecimal("0.0000");
				BigDecimal sum_qty3 = new BigDecimal("0.0000");
				BigDecimal sum_qty4 = new BigDecimal("0.0000");
				BigDecimal sum_qty5 = new BigDecimal("0.0000");
				BigDecimal sum_qty6 = new BigDecimal("0.0000");
				BigDecimal sum_qty7 = new BigDecimal("0.0000");
				BigDecimal sum_qty8 = new BigDecimal("0.0000");
				BigDecimal sum_qty9 = new BigDecimal("0.0000");
				BigDecimal sum_qty10 = new BigDecimal("0.0000");
				BigDecimal sum_qty11 = new BigDecimal("0.0000");
				
				for (Map<String, Object> map : list)
				{
				   sum_qty = sum_qty.add((BigDecimal) map.get("FAuxQty"));
				   sum_qty2 = sum_qty2.add((BigDecimal) map.get("FAmount"));
				   sum_qty3 = sum_qty3.add((BigDecimal) map.get("Y_FAmount"));
				   sum_qty4 = sum_qty4.add((BigDecimal) map.get("Y_FAuxQty"));
				   sum_qty5 = sum_qty5.add((BigDecimal) map.get("LYM_FAuxQty"));
				   sum_qty6 = sum_qty6.add((BigDecimal) map.get("YADD_FAuxQty"));
				   sum_qty7 = sum_qty7.add((BigDecimal) map.get("LM_FAuxQty"));
				   sum_qty8 = sum_qty8.add((BigDecimal) map.get("MADD_FAuxQty"));
				   sum_qty9 = sum_qty9.add((BigDecimal) map.get("FPrice"));
				   sum_qty10 = sum_qty10.add((BigDecimal) map.get("LM_FPrice"));
				   sum_qty11 = sum_qty11.add((BigDecimal) map.get("MADD_PRICE"));
				}
		
				dataGrid.setFooter("FName:合计" + ",FAuxQty:" + sum_qty+ ",FAmount:" + sum_qty2+ ",Y_FAmount:" + sum_qty3+ ",Y_FAuxQty:" + sum_qty4+ ",LYM_FAuxQty:"
						+ sum_qty5+ ",YADD_FAuxQty:" + sum_qty6+ ",LM_FAuxQty:" + sum_qty7 + ",MADD_FAuxQty:" + sum_qty8+ ",FPrice:" + sum_qty9+ ",LM_FPrice:" + sum_qty10+ ",MADD_PRICE:" + sum_qty11);
				
		dataGrid.setTotal(list.size());
		dataGrid.setResults(list);
		TagUtil.datagrid(response, dataGrid);
	}
	

	/**
	 * 
	 * 導出   销售月汇总表
	 * 
	 * @param request
	 * @param response
	 * @param dataGrid
	 */
	@RequestMapping(params = "export_saleTatal")
	public void export_saleTatal(HttpServletRequest request, HttpServletResponse response, DataGrid dataGrid) {

		
		String FDate= request.getParameter("FDate");
		

	String 	sql = "	DECLARE @FNAME varchar(50)                                                                                                                                          ";
		sql += "	DECLARE @RQ smalldatetime                                                                                                                                           ";
		sql += "	DECLARE @RQ_S smalldatetime                                                                                                                                         ";
		sql += "                                                                                                                                                                        ";
		sql += "	DECLARE @YEAR1 INT ,@MONTH1 INT,@YEAR2 INT,@MONTH2 INT                                                                                                              ";
	    sql += " ";
		
		//	sql += "	SET @RQ='2015-10-01'                                                                                                                                                ";
		sql += " SET  @RQ='" + FDate + "-01"+ "'";
		sql += "                                                                                                                                                                        ";
		sql += "	SET @YEAR1=YEAR(@RQ)                                                                                                                                                ";
		sql += "	SET @MONTH1=MONTH(@RQ)                                                                                                                                              ";
		sql += "                                                                                                                                                                        ";
		//sql += "	SET @YEAR2=YEAR(@RQ)-1                                                                                                                                              ";
		//sql += "	SET @MONTH2=MONTH(@RQ)-1                                                                                                                                            ";
		
		sql += " SELECT @RQDAY1_L2=CONVERT(CHAR(10),DATEADD(ms,-3,DATEADD(mm, DATEDIFF(mm,0,@RQ),0)),111)+' 00:00:00' ";
		sql += " SET @MONTH2=  MONTH(@RQDAY1_L2) ";
		sql += " SET @YEAR2=YEAR(@RQDAY1_L2) ";
		
		sql += "                                                                                                                                                                        ";
		sql += "	SELECT T1.FName FName,Convert(decimal(18,4),T1.sum_qty) FAuxQty,T1.sum_amount FAmount,T1.price FPrice,Convert(decimal(18,4),T2.sum_qty) Y_FAuxQty,                  ";
		sql += "	T2.sum_amount Y_FAmount,Convert(decimal(18,4),isnull(T3.sum_qty,0.0000)) LYM_FAuxQty,Convert(decimal(18,4),(T1.sum_qty-isnull(T3.sum_qty,0.0000))) YADD_FAuxQty,    ";
		sql += "	Convert(decimal(18,4),isnull(T4.sum_qty,0.0000)) LM_FAuxQty,Convert(decimal(18,4),(T1.sum_qty -isnull(T4.sum_qty,0.0000))) MADD_FAuxQty,isnull(T4.price,0) LM_FPrice,         ";
		sql += "	isnull((T1.price - T4.price),0) MADD_PRICE                                                                                                                                    ";
		sql += "	 FROM (                                                                                                                                                             ";
		sql += "	SELECT reverse(FName) FName,sum_amount,sum_qty,price FROM (                                                                                                         ";
		sql += "	SELECT substring(reverse(t.FName),1,4) FName,sum(FAuxQty) sum_qty,sum(FAmount) sum_amount,sum(FAmount)/sum(FAuxQty) as price from (                                 ";
		sql += "	SELECT t_ICItem1.FName,ICSaleEntry.FAuxQty,ICSaleEntry.FAmount                                                                                                      ";
		sql += "	FROM  RemoteServer.AIS2009.dbo.ICSale  INNER JOIN RemoteServer.AIS2009.dbo.ICSaleEntry  ON ICSale.FInterID=ICSaleEntry.FInterID                                     ";
		sql += "	 LEFT  JOIN RemoteServer.AIS2009.dbo.t_ICItem t_ICItem1 ON ICSaleEntry.FItemID=t_ICItem1.FItemID AND t_ICItem1.FItemID<>0                                           ";
		sql += "	 WHERE 1=1                                                                                                                                                          ";
		sql += "	 AND ICSale.FYear =  @YEAR1                                                                                                                                         ";
		sql += "	 AND ICSale.FPeriod =  @MONTH1                                                                                                                                      ";
		sql += "	 AND isnull(ICSale.FSaleStyle,0)=101 AND isnull(ICSale.FCheckerID,0)>0 AND ICSale.FClassTypeID=1000002                                                              ";
		sql += "	 AND ( ICSale.FSubSystemID>=0 and ICSale.finterid in(select finvoiceid from RemoteServer.AIS2009.dbo.t_rp_contact                                                   ";
		sql += "	 where ftype=3 and finvoiceid>0) and isnull(ICSale.FCancellation,0)=0 )                                                                                             ";
		sql += "	 ) t                                                                                                                                                                ";
		sql += "	group by substring(reverse(t.FName),1,4)                                                                                                                            ";
		sql += "	)tt                                                                                                                                                                 ";
		sql += "	) T1                                                                                                                                                                ";
		sql += "                                                                                                                                                                        ";
		sql += "	LEFT JOIN                                                                                                                                                           ";
		sql += "                                                                                                                                                                        ";
		sql += "	(                                                                                                                                                                   ";
		sql += "	SELECT  reverse(FName) FName,sum_qty,sum_amount FROM (                                                                                                              ";
		sql += "	SELECT substring(reverse(t.FName),1,4) FName,sum(FAuxQty) sum_qty,sum(FAmount) sum_amount FROM(                                                                     ";
		sql += "	SELECT t_ICItem1.FName,ICSaleEntry.FAuxQty,ICSaleEntry.FAmount                                                                                                      ";
		sql += "	FROM  RemoteServer.AIS2009.dbo.ICSale  INNER JOIN RemoteServer.AIS2009.dbo.ICSaleEntry  ON ICSale.FInterID=ICSaleEntry.FInterID                                     ";
		sql += "	 LEFT  JOIN RemoteServer.AIS2009.dbo.t_ICItem t_ICItem1 ON ICSaleEntry.FItemID=t_ICItem1.FItemID AND t_ICItem1.FItemID<>0                                           ";
		sql += "	 WHERE  1=1                                                                                                                                                         ";
		sql += "	 AND ICSale.FYear = @YEAR1                                                                                                                                          ";
		sql += " AND ICSale.FPeriod <=  @MONTH1   ";
		sql += "	 AND isnull(ICSale.FSaleStyle,0)=101 AND isnull(ICSale.FCheckerID,0)>0 AND ICSale.FClassTypeID=1000002                                                              ";
		sql += "	 AND ( ICSale.FSubSystemID>=0 and ICSale.finterid in(select finvoiceid from RemoteServer.AIS2009.dbo.t_rp_contact                                                   ";
		sql += "	 where ftype=3 and finvoiceid>0) and isnull(ICSale.FCancellation,0)=0 )                                                                                             ";
		sql += "	 )t                                                                                                                                                                 ";
		sql += "	group by substring(reverse(t.FName),1,4)                                                                                                                            ";
		sql += "	) tt                                                                                                                                                                ";
		sql += "	) T2 ON T1.FName = T2.FName                                                                                                                                         ";
		sql += "                                                                                                                                                                        ";
		sql += "	LEFT JOIN                                                                                                                                                           ";
		sql += "                                                                                                                                                                        ";
		sql += "	(                                                                                                                                                                   ";
		sql += "	SELECT  reverse(FName) FName,sum_qty FROM (                                                                                                                         ";
		sql += "	SELECT substring(reverse(t.FName),1,4) FName,sum(FAuxQty) sum_qty from (                                                                                            ";
		sql += "	SELECT t_ICItem1.FName,ICSaleEntry.FAuxQty                                                                                                                          ";
		sql += "	FROM  RemoteServer.AIS2009.dbo.ICSale  INNER JOIN RemoteServer.AIS2009.dbo.ICSaleEntry  ON ICSale.FInterID=ICSaleEntry.FInterID                                     ";
		sql += "	 LEFT  JOIN RemoteServer.AIS2009.dbo.t_ICItem t_ICItem1 ON ICSaleEntry.FItemID=t_ICItem1.FItemID AND t_ICItem1.FItemID<>0                                           ";
		sql += "	 WHERE  1=1                                                                                                                                                         ";
		sql += "	 AND ICSale.FYear =  @YEAR2                                                                                                                                         ";
		sql += "	 AND ICSale.FPeriod =  @MONTH1                                                                                                                                      ";
		sql += "	 AND isnull(ICSale.FSaleStyle,0)=101 AND isnull(ICSale.FCheckerID,0)>0 AND ICSale.FClassTypeID=1000002                                                              ";
		sql += "	 AND ( ICSale.FSubSystemID>=0 and ICSale.finterid in(select finvoiceid from RemoteServer.AIS2009.dbo.t_rp_contact                                                   ";
		sql += "	 where ftype=3 and finvoiceid>0) and isnull(ICSale.FCancellation,0)=0 )                                                                                             ";
		sql += "	 ) t                                                                                                                                                                ";
		sql += "	group by substring(reverse(t.FName),1,4)                                                                                                                            ";
		sql += "	)tt                                                                                                                                                                 ";
		sql += "	) T3 ON T1.FName = T3.FName                                                                                                                                         ";
		sql += "                                                                                                                                                                        ";
		sql += "	LEFT JOIN                                                                                                                                                           ";
		sql += "                                                                                                                                                                        ";
		sql += "	(                                                                                                                                                                   ";
		sql += "	SELECT  reverse(FName) FName,sum_qty,price FROM (                                                                                                                   ";
		sql += "	SELECT substring(reverse(t.FName),1,4) FName,sum(FAuxQty) sum_qty,sum(FAmount)/sum(FAuxQty) as price from (                                                         ";
		sql += "	select t_ICItem1.FName,ICSaleEntry.FAuxQty,ICSaleEntry.FAmount                                                                                                      ";
		sql += "	FROM  RemoteServer.AIS2009.dbo.ICSale  INNER JOIN RemoteServer.AIS2009.dbo.ICSaleEntry  ON ICSale.FInterID=ICSaleEntry.FInterID                                     ";
		sql += "	 LEFT  JOIN RemoteServer.AIS2009.dbo.t_ICItem t_ICItem1 ON ICSaleEntry.FItemID=t_ICItem1.FItemID AND t_ICItem1.FItemID<>0                                           ";
		sql += "	 WHERE 1=1                                                                                                                                                          ";
		sql += "	 AND ICSale.FYear =  @YEAR2                                                                                                                                         ";
		sql += "	 AND ICSale.FPeriod =  @MONTH2                                                                                                                                      ";
		sql += "	 AND isnull(ICSale.FSaleStyle,0)=101 AND isnull(ICSale.FCheckerID,0)>0 AND ICSale.FClassTypeID=1000002                                                              ";
		sql += "	 AND ( ICSale.FSubSystemID>=0 and ICSale.finterid in(select finvoiceid from RemoteServer.AIS2009.dbo.t_rp_contact                                                   ";
		sql += "	 where ftype=3 and finvoiceid>0) and isnull(ICSale.FCancellation,0)=0 )                                                                                             ";
		sql += "	 ) t                                                                                                                                                                ";
		sql += "	group by substring(reverse(t.FName),1,4)                                                                                                                            ";
		sql += "	)tt                                                                                                                                                                 ";
		sql += "	) T4 ON T1.FName = T4.FName                                                                                                                                         ";



		//List<Map> list = saleMiniDao.execContract3(sql);
		
		List<Map> maplist = null;

		try {
			maplist = saleMiniDao.execContract3(sql);
			List<String[]> list = new ArrayList<String[]>();

			for (Map<String, Object> map : maplist) {
				String[] arr = new String[map.size()];
				arr[0] = (String) map.get("FName"); 
				arr[1] =  String.valueOf((BigDecimal) map.get("FAuxQty"));
				arr[2] =  String.valueOf((BigDecimal) map.get("FAmount"));
				arr[3] = String.valueOf((BigDecimal) map.get("Y_FAmount"));
				arr[4] =  String.valueOf((BigDecimal) map.get("Y_FAuxQty"));
				arr[5] =   String.valueOf((BigDecimal) map.get("LYM_FAuxQty"));
				arr[6] =  String.valueOf((BigDecimal) map.get("YADD_FAuxQty"));
				arr[7] =  String.valueOf((BigDecimal) map.get("LM_FAuxQty"));
				arr[8] = String.valueOf((BigDecimal) map.get("MADD_FAuxQty"));
				arr[9] = String.valueOf((BigDecimal) map.get("FPrice"));
				arr[10] =  String.valueOf((BigDecimal) map.get("LM_FPrice"));
				arr[11] = String.valueOf((BigDecimal) map.get("MADD_PRICE"));
				list.add(arr);

			}
            
			 // 数量合计
			BigDecimal sum_qty = new BigDecimal("0.0000");
			BigDecimal sum_qty2 = new BigDecimal("0.0000");
			BigDecimal sum_qty3 = new BigDecimal("0.0000");
			BigDecimal sum_qty4 = new BigDecimal("0.0000");
			BigDecimal sum_qty5 = new BigDecimal("0.0000");
			BigDecimal sum_qty6 = new BigDecimal("0.0000");
			BigDecimal sum_qty7 = new BigDecimal("0.0000");
			BigDecimal sum_qty8 = new BigDecimal("0.0000");
			BigDecimal sum_qty9 = new BigDecimal("0.0000");
			BigDecimal sum_qty10 = new BigDecimal("0.0000");
			BigDecimal sum_qty11 = new BigDecimal("0.0000");
			
			for (Map<String, Object> map : maplist)
			{
			   sum_qty = sum_qty.add((BigDecimal) map.get("FAuxQty"));
			   sum_qty2 = sum_qty2.add((BigDecimal) map.get("FAmount"));
			   sum_qty3 = sum_qty3.add((BigDecimal) map.get("Y_FAmount"));
			   sum_qty4 = sum_qty4.add((BigDecimal) map.get("Y_FAuxQty"));
			   sum_qty5 = sum_qty5.add((BigDecimal) map.get("LYM_FAuxQty"));
			   sum_qty6 = sum_qty6.add((BigDecimal) map.get("YADD_FAuxQty"));
			   sum_qty7 = sum_qty7.add((BigDecimal) map.get("LM_FAuxQty"));
			   sum_qty8 = sum_qty8.add((BigDecimal) map.get("MADD_FAuxQty"));
			   sum_qty9 = sum_qty9.add((BigDecimal) map.get("FPrice"));
			   sum_qty10 = sum_qty10.add((BigDecimal) map.get("LM_FPrice"));
			   sum_qty11 = sum_qty11.add((BigDecimal) map.get("MADD_PRICE"));
			}
			
			String[] sums = new String[]{"合计",String.valueOf(sum_qty),String.valueOf(sum_qty2),String.valueOf(sum_qty3),String.valueOf(sum_qty4),String.valueOf(sum_qty5),String.valueOf(sum_qty6),String.valueOf(sum_qty7),String.valueOf(sum_qty8),
					String.valueOf(sum_qty9),String.valueOf(sum_qty10),String.valueOf(sum_qty11)};
			list.add(sums);	
			
			String[] columnNames = new String[] { "物料名称", "本月销售量", "本月销售额（不含税）", "本年度销售额累计（不含税）", "本年度销售量累计", "去年同期销售量累计", "销售量增减（同比）", "上月销售量", "销售量增减（环比）",
					"本月销售平均单价（不含税）", "上月销售平均单价（不含税）", "销售单价增减（不含税）"};
			String titleName = "销售月汇总表.xls";

			OutputStream fOut = response.getOutputStream();
			response.setHeader("Content-Disposition", "attachment; filename="
					+ new String(titleName.getBytes("GB2312"), "ISO8859-1"));

			ExcelUtils.create(request, list, columnNames, titleName, fOut);

		} catch (Exception ex) {

		}

	}
	
	
	
	/**
	 * easyui AJAX请求数据 销售开票月报表
	 * 
	 * @param request
	 * @param response
	 * @param dataGrid
	 * @param user
	 */

	@RequestMapping(params = "datagrid4")
	public void datagrid4(HttpServletRequest request, HttpServletResponse response, DataGrid dataGrid) {

		String FDate_begin = request.getParameter("FDate_begin");
		String FDate_end = request.getParameter("FDate_end");
		String FItemName = request.getParameter("FItemName");
		
		String FName = request.getParameter("FName");
		
        
        

      String  sql ="	DECLARE @RQ1 smalldatetime                                                                                                                ";
        sql+="	DECLARE @RQ2 smalldatetime                                                                                                                ";
        sql+="	DECLARE @RQDAY1_L1 smalldatetime                                                                                                          ";
        sql+="	DECLARE @RQDAY1_L2 smalldatetime                                                                                                          ";
        sql+="	DECLARE @Name varchar(50)                                                                                                                 ";
        sql+="                                                                                                                                            ";
        //sql+="	SET @RQ1='2015-12-01'                                                                                                                     ";
       // sql+="	SET @RQ2='2015-12-31'                                                                                                                     ";
     //   sql+="	SET @Name = '%冷轧钢带%'                                                                                                                  ";
       
        sql+="		SET @RQ1='" + FDate_begin + "'"; 
        sql+="		SET @RQ2='" + FDate_end  + "'"; 
        sql+="		SET @Name ='%" +  FItemName + "%'"; 
        
        
        sql+="                                                                                                                                            ";
        sql+="                                                                                                                                            ";
        sql+="                                                                                                                                            ";
        sql+="	SELECT @RQDAY1_L1= CONVERT(CHAR(10),DATEADD(month,-1,DATEADD(dd,-DAY(@RQ2)+1,@RQ2)),111)                                                  ";
        sql+="	SELECT @RQDAY1_L2=CONVERT(CHAR(10),DATEADD(ms,-3,DATEADD(mm, DATEDIFF(mm,0,@RQ2),0)),111)+' 00:00:00'                                     ";
        sql+="	                                                                                                                                          ";
        sql+="	select *,(Qty2 - Qty3 + Qty1) Qty4 from (                                                                                                 ";
        sql+="	select T1.FName,Convert(decimal(18,4),(isnull(T2.sum_qty,0.0000) -isnull(T3.sum_qty,0.0000))) Qty1,Convert(decimal(18,4),                 ";
        sql+="	isnull(T1.sum_qty,0.0000)) Qty2,Convert(decimal(18,4),isnull(T4.sum_qty,0.0000)) Qty3                                                     ";
        sql+="	 from                                                                                                                                     ";
        sql+="	(                                                                                                                                         ";
        sql+="	 select  t4.FName,sum(isnull(t2.FQty,0.0000))/1000 sum_qty from RemoteServer.AIS2009.dbo.IcStockBill t1                                   ";
        sql+="	 inner join RemoteServer.AIS2009.dbo.ICStockBillEntry t2 on t1.FInterID = t2.FInterID                                                     ";
        sql+="	 inner join RemoteServer.AIS2009.dbo.t_ICItem t3 on t2.FItemID = t3.FItemID                                                               ";
        sql+="	 INNER JOIN RemoteServer.AIS2009.dbo.t_Organization t4 ON     t1.FSupplyID = t4.FItemID   AND t4.FItemID <>0                              ";
        sql+="	 where FTranType=21 and isnull(FCheckerID,0)<>0                                                                                           ";
        sql+="	 and t3.FName like @Name                                                                                                                  ";
        sql+="	 and t1.FDate <= @RQ2                                                                                                                     ";
        sql+="	 and t1.FDate >= @RQ1                                                                                                                     ";
        sql+="	 group by t4.FName                                                                                                                        ";
        sql+="	 ) T1                                                                                                                                     ";
        sql+="	                                                                                                                                          ";
        sql+="	 LEFT JOIN                                                                                                                                ";
        sql+="	                                                                                                                                          ";
        sql+="	 (                                                                                                                                        ";
        sql+="	 select  t4.FName,sum(isnull(t2.FQty,0.0000))/1000 sum_qty from RemoteServer.AIS2009.dbo.IcStockBill t1                                   ";
        sql+="	 inner join RemoteServer.AIS2009.dbo.ICStockBillEntry t2 on t1.FInterID = t2.FInterID                                                     ";
        sql+="	 inner join RemoteServer.AIS2009.dbo.t_ICItem t3 on t2.FItemID = t3.FItemID                                                               ";
        sql+="	 INNER JOIN RemoteServer.AIS2009.dbo.t_Organization t4 ON     t1.FSupplyID = t4.FItemID   AND t4.FItemID <>0                              ";
        sql+="	 where FTranType=21 and isnull(FCheckerID,0)<>0                                                                                           ";
        sql+="	  and t3.FName like @Name                                                                                                                 ";
        sql+="	 and t1.FDate <= @RQDAY1_L2                                                                                                               ";
        sql+="	 and t1.FDate >= @RQDAY1_L1                                                                                                               ";
        sql+="	 group by t4.FName                                                                                                                        ";
        sql+="	 ) T2 on T1.FName = T2.FName                                                                                                              ";
        sql+="	                                                                                                                                          ";
        sql+="	 LEFT JOIN                                                                                                                                ";
        sql+="	                                                                                                                                          ";
        sql+="	 (                                                                                                                                        ";
        sql+="	 SELECT t_Item.FName,sum(isnull(ICSaleEntry.FAuxQty,0.0000)) sum_qty                                                                      ";
        sql+="	FROM  RemoteServer.AIS2009.dbo.ICSale  INNER JOIN RemoteServer.AIS2009.dbo.ICSaleEntry  ON ICSale.FInterID=ICSaleEntry.FInterID           ";
        sql+="	 LEFT  JOIN RemoteServer.AIS2009.dbo.t_ICItem t_ICItem1 ON ICSaleEntry.FItemID=t_ICItem1.FItemID AND t_ICItem1.FItemID<>0                 ";
        sql+="	 LEFT  JOIN RemoteServer.AIS2009.dbo.t_Item t_Item ON ICSale.FCustID=t_Item.FItemID AND t_Item.FItemID<>0                                 ";
        sql+="	 WHERE 1=1                                                                                                                                ";
        sql+="	 and  t_ICItem1.FName like @Name                                                                                                          ";
        sql+="	 AND ICSale.FDate <=  @RQDAY1_L2                                                                                                          ";
        sql+="	 AND ICSale.FDate >=  @RQDAY1_L1                                                                                                          ";
        sql+="	 AND isnull(ICSale.FSaleStyle,0)=101 AND isnull(ICSale.FCheckerID,0)>0 AND ICSale.FClassTypeID=1000002                                    ";
        sql+="	 AND ( ICSale.FSubSystemID>=0 and ICSale.finterid in(select finvoiceid from RemoteServer.AIS2009.dbo.t_rp_contact                         ";
        sql+="	 where ftype=3 and finvoiceid>0) and isnull(ICSale.FCancellation,0)=0 )                                                                   ";
        sql+="	 group by t_Item.FName                                                                                                                    ";
        sql+="	 ) T3 ON T1.FName = T3.FName                                                                                                              ";
        sql+="	                                                                                                                                          ";
        sql+="	 LEFT JOIN                                                                                                                                ";
        sql+="	                                                                                                                                          ";
        sql+="	 (                                                                                                                                        ";
        sql+="                                                                                                                                            ";
        sql+="	  SELECT t_Item.FName,sum(isnull(ICSaleEntry.FAuxQty,0.0000)) sum_qty                                                                     ";
        sql+="	FROM  RemoteServer.AIS2009.dbo.ICSale  INNER JOIN RemoteServer.AIS2009.dbo.ICSaleEntry  ON ICSale.FInterID=ICSaleEntry.FInterID           ";
        sql+="	 LEFT  JOIN RemoteServer.AIS2009.dbo.t_ICItem t_ICItem1 ON ICSaleEntry.FItemID=t_ICItem1.FItemID AND t_ICItem1.FItemID<>0                 ";
        sql+="	 LEFT  JOIN RemoteServer.AIS2009.dbo.t_Item t_Item ON ICSale.FCustID=t_Item.FItemID AND t_Item.FItemID<>0                                 ";
        sql+="	 WHERE 1=1                                                                                                                                ";
        sql+="	 and  t_ICItem1.FName like @Name                                                                                                          ";
        sql+="	 AND ICSale.FDate <=  @RQ2                                                                                                                ";
        sql+="	 AND ICSale.FDate >=  @RQ1                                                                                                                ";
        sql+="	 AND isnull(ICSale.FSaleStyle,0)=101 AND isnull(ICSale.FCheckerID,0)>0 AND ICSale.FClassTypeID=1000002                                    ";
        sql+="	 AND ( ICSale.FSubSystemID>=0 and ICSale.finterid in(select finvoiceid from RemoteServer.AIS2009.dbo.t_rp_contact                         ";
        sql+="	 where ftype=3 and finvoiceid>0) and isnull(ICSale.FCancellation,0)=0 )                                                                   ";
        sql+="	 group by t_Item.FName                                                                                                                    ";
        sql+="	 ) T4 ON T1.FName = T4.FName                                                                                                              ";
        sql+="	) T        ";
		
		if(StringUtil.isNotEmpty(FName)){
			
			sql += " where FName like '%" + FName + "%'";
		}
	
		List<Map> list = saleMiniDao.execContract4(sql);
		

		dataGrid.setTotal(list.size());
		dataGrid.setResults(list);
		
		   // 数量合计
				BigDecimal sum_qty = new BigDecimal("0.0000");
				BigDecimal sum_qty2 = new BigDecimal("0.0000");
				BigDecimal sum_qty3 = new BigDecimal("0.0000");
				BigDecimal sum_qty4 = new BigDecimal("0.0000");
				
				for (Map<String, Object> map : list)
				{
				   sum_qty = sum_qty.add((BigDecimal) map.get("Qty1"));
				   sum_qty2 = sum_qty2.add((BigDecimal) map.get("Qty2"));
				   sum_qty3 = sum_qty3.add((BigDecimal) map.get("Qty3"));
				   sum_qty4 = sum_qty4.add((BigDecimal) map.get("Qty4"));
				}
				
				dataGrid.setFooter("FName:合计" + ",Qty1:" + sum_qty+ ",Qty2:" + sum_qty2+ ",Qty3:" + sum_qty3+ ",Qty4:" + sum_qty4);
		TagUtil.datagrid(response, dataGrid);
	}
	
	
	
	/**
	 * 
	 * 導出  销售开票月报表
	 * 
	 * @param request
	 * @param response
	 * @param dataGrid
	 */
	@RequestMapping(params = "export_xskp")
	public void export_xskp(HttpServletRequest request, HttpServletResponse response, DataGrid dataGrid) {

		String FDate_begin = request.getParameter("FDate_begin");
		String FDate_end = request.getParameter("FDate_end");
		String FItemName = request.getParameter("FItemName");
		String FName = request.getParameter("FName");
        
        

      String  sql ="	DECLARE @RQ1 smalldatetime                                                                                                                ";
        sql+="	DECLARE @RQ2 smalldatetime                                                                                                                ";
        sql+="	DECLARE @RQDAY1_L1 smalldatetime                                                                                                          ";
        sql+="	DECLARE @RQDAY1_L2 smalldatetime                                                                                                          ";
        sql+="	DECLARE @Name varchar(50)                                                                                                                 ";
        sql+="                                                                                                                                            ";
        //sql+="	SET @RQ1='2015-12-01'                                                                                                                     ";
       // sql+="	SET @RQ2='2015-12-31'                                                                                                                     ";
     //   sql+="	SET @Name = '%冷轧钢带%'                                                                                                                  ";
       
        sql+="		SET @RQ1='" + FDate_begin + "'"; 
        sql+="		SET @RQ2='" + FDate_end  + "'"; 
        sql+="		SET @Name ='%" +  FItemName + "%'"; 
        
        
        sql+="                                                                                                                                            ";
        sql+="                                                                                                                                            ";
        sql+="                                                                                                                                            ";
        sql+="	SELECT @RQDAY1_L1= CONVERT(CHAR(10),DATEADD(month,-1,DATEADD(dd,-DAY(@RQ2)+1,@RQ2)),111)                                                  ";
        sql+="	SELECT @RQDAY1_L2=CONVERT(CHAR(10),DATEADD(ms,-3,DATEADD(mm, DATEDIFF(mm,0,@RQ2),0)),111)+' 00:00:00'                                     ";
        sql+="	                                                                                                                                          ";
        sql+="	select *,(Qty2 - Qty3 + Qty1) Qty4 from (                                                                                                 ";
        sql+="	select T1.FName,Convert(decimal(18,4),(isnull(T2.sum_qty,0.0000) -isnull(T3.sum_qty,0.0000))) Qty1,Convert(decimal(18,4),                 ";
        sql+="	isnull(T1.sum_qty,0.0000)) Qty2,Convert(decimal(18,4),isnull(T4.sum_qty,0.0000)) Qty3                                                     ";
        sql+="	 from                                                                                                                                     ";
        sql+="	(                                                                                                                                         ";
        sql+="	 select  t4.FName,sum(isnull(t2.FQty,0.0000))/1000 sum_qty from RemoteServer.AIS2009.dbo.IcStockBill t1                                   ";
        sql+="	 inner join RemoteServer.AIS2009.dbo.ICStockBillEntry t2 on t1.FInterID = t2.FInterID                                                     ";
        sql+="	 inner join RemoteServer.AIS2009.dbo.t_ICItem t3 on t2.FItemID = t3.FItemID                                                               ";
        sql+="	 INNER JOIN RemoteServer.AIS2009.dbo.t_Organization t4 ON     t1.FSupplyID = t4.FItemID   AND t4.FItemID <>0                              ";
        sql+="	 where FTranType=21 and isnull(FCheckerID,0)<>0                                                                                           ";
        sql+="	 and t3.FName like @Name                                                                                                                  ";
        sql+="	 and t1.FDate <= @RQ2                                                                                                                     ";
        sql+="	 and t1.FDate >= @RQ1                                                                                                                     ";
        sql+="	 group by t4.FName                                                                                                                        ";
        sql+="	 ) T1                                                                                                                                     ";
        sql+="	                                                                                                                                          ";
        sql+="	 LEFT JOIN                                                                                                                                ";
        sql+="	                                                                                                                                          ";
        sql+="	 (                                                                                                                                        ";
        sql+="	 select  t4.FName,sum(isnull(t2.FQty,0.0000))/1000 sum_qty from RemoteServer.AIS2009.dbo.IcStockBill t1                                   ";
        sql+="	 inner join RemoteServer.AIS2009.dbo.ICStockBillEntry t2 on t1.FInterID = t2.FInterID                                                     ";
        sql+="	 inner join RemoteServer.AIS2009.dbo.t_ICItem t3 on t2.FItemID = t3.FItemID                                                               ";
        sql+="	 INNER JOIN RemoteServer.AIS2009.dbo.t_Organization t4 ON     t1.FSupplyID = t4.FItemID   AND t4.FItemID <>0                              ";
        sql+="	 where FTranType=21 and isnull(FCheckerID,0)<>0                                                                                           ";
        sql+="	  and t3.FName like @Name                                                                                                                 ";
        sql+="	 and t1.FDate <= @RQDAY1_L2                                                                                                               ";
        sql+="	 and t1.FDate >= @RQDAY1_L1                                                                                                               ";
        sql+="	 group by t4.FName                                                                                                                        ";
        sql+="	 ) T2 on T1.FName = T2.FName                                                                                                              ";
        sql+="	                                                                                                                                          ";
        sql+="	 LEFT JOIN                                                                                                                                ";
        sql+="	                                                                                                                                          ";
        sql+="	 (                                                                                                                                        ";
        sql+="	 SELECT t_Item.FName,sum(isnull(ICSaleEntry.FAuxQty,0.0000)) sum_qty                                                                      ";
        sql+="	FROM  RemoteServer.AIS2009.dbo.ICSale  INNER JOIN RemoteServer.AIS2009.dbo.ICSaleEntry  ON ICSale.FInterID=ICSaleEntry.FInterID           ";
        sql+="	 LEFT  JOIN RemoteServer.AIS2009.dbo.t_ICItem t_ICItem1 ON ICSaleEntry.FItemID=t_ICItem1.FItemID AND t_ICItem1.FItemID<>0                 ";
        sql+="	 LEFT  JOIN RemoteServer.AIS2009.dbo.t_Item t_Item ON ICSale.FCustID=t_Item.FItemID AND t_Item.FItemID<>0                                 ";
        sql+="	 WHERE 1=1                                                                                                                                ";
        sql+="	 and  t_ICItem1.FName like @Name                                                                                                          ";
        sql+="	 AND ICSale.FDate <=  @RQDAY1_L2                                                                                                          ";
        sql+="	 AND ICSale.FDate >=  @RQDAY1_L1                                                                                                          ";
        sql+="	 AND isnull(ICSale.FSaleStyle,0)=101 AND isnull(ICSale.FCheckerID,0)>0 AND ICSale.FClassTypeID=1000002                                    ";
        sql+="	 AND ( ICSale.FSubSystemID>=0 and ICSale.finterid in(select finvoiceid from RemoteServer.AIS2009.dbo.t_rp_contact                         ";
        sql+="	 where ftype=3 and finvoiceid>0) and isnull(ICSale.FCancellation,0)=0 )                                                                   ";
        sql+="	 group by t_Item.FName                                                                                                                    ";
        sql+="	 ) T3 ON T1.FName = T3.FName                                                                                                              ";
        sql+="	                                                                                                                                          ";
        sql+="	 LEFT JOIN                                                                                                                                ";
        sql+="	                                                                                                                                          ";
        sql+="	 (                                                                                                                                        ";
        sql+="                                                                                                                                            ";
        sql+="	  SELECT t_Item.FName,sum(isnull(ICSaleEntry.FAuxQty,0.0000)) sum_qty                                                                     ";
        sql+="	FROM  RemoteServer.AIS2009.dbo.ICSale  INNER JOIN RemoteServer.AIS2009.dbo.ICSaleEntry  ON ICSale.FInterID=ICSaleEntry.FInterID           ";
        sql+="	 LEFT  JOIN RemoteServer.AIS2009.dbo.t_ICItem t_ICItem1 ON ICSaleEntry.FItemID=t_ICItem1.FItemID AND t_ICItem1.FItemID<>0                 ";
        sql+="	 LEFT  JOIN RemoteServer.AIS2009.dbo.t_Item t_Item ON ICSale.FCustID=t_Item.FItemID AND t_Item.FItemID<>0                                 ";
        sql+="	 WHERE 1=1                                                                                                                                ";
        sql+="	 and  t_ICItem1.FName like @Name                                                                                                          ";
        sql+="	 AND ICSale.FDate <=  @RQ2                                                                                                                ";
        sql+="	 AND ICSale.FDate >=  @RQ1                                                                                                                ";
        sql+="	 AND isnull(ICSale.FSaleStyle,0)=101 AND isnull(ICSale.FCheckerID,0)>0 AND ICSale.FClassTypeID=1000002                                    ";
        sql+="	 AND ( ICSale.FSubSystemID>=0 and ICSale.finterid in(select finvoiceid from RemoteServer.AIS2009.dbo.t_rp_contact                         ";
        sql+="	 where ftype=3 and finvoiceid>0) and isnull(ICSale.FCancellation,0)=0 )                                                                   ";
        sql+="	 group by t_Item.FName                                                                                                                    ";
        sql+="	 ) T4 ON T1.FName = T4.FName                                                                                                              ";
        sql+="	) T        ";
		
         if(StringUtil.isNotEmpty(FName)){
			
			sql += " where FName like '%" + FName + "%'";
		}
		
	
		//List<Map> list = saleMiniDao.execContract4(sql);

        List<Map> maplist = null;

		try {
			maplist = saleMiniDao.execContract4(sql);
			List<String[]> list = new ArrayList<String[]>();

			for (Map<String, Object> map : maplist) {
				String[] arr = new String[map.size()];
				arr[0] = (String) map.get("FName");
				arr[1] = String.valueOf((BigDecimal) map.get("Qty1"));
				arr[2] =  String.valueOf((BigDecimal) map.get("Qty2"));
				arr[3] =  String.valueOf((BigDecimal) map.get("Qty3"));
				arr[4] =  String.valueOf((BigDecimal) map.get("Qty4"));
				list.add(arr);

			}
			// 数量合计
			BigDecimal sum_qty = new BigDecimal("0.0000");
			BigDecimal sum_qty2 = new BigDecimal("0.0000");
			BigDecimal sum_qty3 = new BigDecimal("0.0000");
			BigDecimal sum_qty4 = new BigDecimal("0.0000");
			
			for (Map<String, Object> map : maplist)
			{
			   sum_qty = sum_qty.add((BigDecimal) map.get("Qty1"));
			   sum_qty2 = sum_qty2.add((BigDecimal) map.get("Qty2"));
			   sum_qty3 = sum_qty3.add((BigDecimal) map.get("Qty3"));
			   sum_qty4 = sum_qty4.add((BigDecimal) map.get("Qty4"));
			}
			
			String[] sums = new String[]{"合计",String.valueOf(sum_qty),String.valueOf(sum_qty2),String.valueOf(sum_qty3),String.valueOf(sum_qty4)};
			list.add(sums);
			String[] columnNames = new String[] { "客户名称", "上期发货未开票数(吨)", "本月发货数量(吨)", "本月财务开票数(吨)(实际开票数)", "月末结存发货未开票数(吨)"};
			String titleName = "销售开票月报表.xls";

			OutputStream fOut = response.getOutputStream();
			response.setHeader("Content-Disposition", "attachment; filename="
					+ new String(titleName.getBytes("GB2312"), "ISO8859-1"));

			ExcelUtils.create(request, list, columnNames, titleName, fOut);

		} catch (Exception ex) {

		}

	}
	
	
	
	
	
	/**
	 * easyui AJAX请求数据 采购价格分析
	 * 
	 * @param request
	 * @param response
	 * @param dataGrid
	 * @param user
	 */

	@RequestMapping(params = "datagrid6")
	public void datagrid6(HttpServletRequest request, HttpServletResponse response, DataGrid dataGrid) {
		
		String FDate = request.getParameter("FDate");
		//sql+="	-- 采购价格分析                                                                                                                                                           ";   
		String sql="	DECLARE @FNAME varchar(50)                                                                                                                                                ";
		sql+="	DECLARE @RQ smalldatetime                                                                                                                                                 ";
		sql+="	DECLARE @RQDAY1_L1 SMALLDATETIME                                                                                                                                          ";
		sql+="	DECLARE @RQDAY1_L2 SMALLDATETIME                                                                                                                                          ";
		sql+="                                                                                                                                                                            ";
		sql+="	DECLARE @YEAR1 INT ,@MONTH1 INT,@YEAR2 INT,@MONTH2 INT                                                                                                                    ";
	//	sql+="	SET @RQ='2014-10-01'                                                                                                                                                      ";
		sql+="	SET @RQ='" + FDate + "-01'"; 
		sql+="                                                                                                                                                                            ";
		sql+="	SET @YEAR1=YEAR(@RQ)                                                                                                                                                      ";
		sql+="	SET @MONTH1=MONTH(@RQ)                                                                                                                                                    ";
		sql+="                                                                                                                                                                            ";
		sql+="	SET @YEAR2=YEAR(@RQ)-1                                                                                                                                                    ";
		sql+="	SET @MONTH2=MONTH(@RQ)-1                                                                                                                                                  ";
		sql+="	                                                                                                                                                                          ";
		//sql+="	--上月第一天                                                                                                                                                              ";
		sql+="	SELECT @RQDAY1_L1= CONVERT(CHAR(10),DATEADD(month,-1,DATEADD(dd,-DAY(@RQ)+1,@RQ)),111)                                                                                    ";
		//sql+="	--上月最后一天                                                                                                                                                            ";
		sql+="	SELECT @RQDAY1_L2=CONVERT(CHAR(10),DATEADD(ms,-3,DATEADD(mm, DATEDIFF(mm,0,@RQ),0)),111)+' 00:00:00'                                                                      ";
		sql+="	                                                                                                                                                                          ";
		sql+="	 select *,(FPrice1 - FPrice2) FPrice4,(FPrice1 - FPrice3) FPrice5  from (                                                                                                 ";
		sql+="	 select V1.FName,Convert(decimal(18,4),isnull(V1.FPrice,0)) FPrice1,Convert(decimal(18,4),isnull(V2.FPrice2,0)) FPrice2,                                                  ";
		sql+="	Convert(decimal(18,4),isnull(V3.FPrice3,0)) FPrice3                                                                                                                       ";
		sql+="	 from (                                                                                                                                                                   ";
		//sql+="	 -- 本月平均单价                                                                                                                                                          ";
		sql+="		select v2.FName,avg(u1.Fauxprice) FPrice                                                                                                                              ";
		sql+="		from RemoteServer.AIS2009.dbo.ICStockBill v1 INNER JOIN RemoteServer.AIS2009.dbo.ICStockBillEntry u1 ON     v1.FInterID = u1.FInterID   AND u1.FInterID <>0           ";
		sql+="		LEFT JOIN RemoteServer.AIS2009.dbo.t_ICItem  v2 ON u1.FItemID=v2.FItemID                                                                                              ";
		sql+="		where 1=1 AND  (v1.FTranType=1 AND ((v1.FCheckerID>0)  and  v1.FCancellation = 0))                                                                                    ";
		sql+="		and v2.FNumber like 'Y.%'                                                                                                                                             ";
		sql+="		and YEAR(v1.FDate) = @YEAR1  and MONTH(v1.FDate) = @MONTH1                                                                                                            ";
		sql+="		group by v2.FName                                                                                                                                                     ";
		sql+="		 ) V1                                                                                                                                                                 ";
		sql+="		                                                                                                                                                                      ";
		sql+="		 LEFT JOIN                                                                                                                                                            ";
		sql+="		                                                                                                                                                                      ";
		sql+="		 (                                                                                                                                                                    ";
		//sql+="	 -- 上月平均单价                                                                                                                                                          ";
		sql+="		select v2.FName,avg(u1.Fauxprice) FPrice2                                                                                                                             ";
		sql+="		from RemoteServer.AIS2009.dbo.ICStockBill v1 INNER JOIN RemoteServer.AIS2009.dbo.ICStockBillEntry u1 ON     v1.FInterID = u1.FInterID   AND u1.FInterID <>0           ";
		sql+="		LEFT JOIN RemoteServer.AIS2009.dbo.t_ICItem  v2 ON u1.FItemID=v2.FItemID                                                                                              ";
		sql+="		where 1=1 AND  (v1.FTranType=1 AND ((v1.FCheckerID>0)  and  v1.FCancellation = 0))                                                                                    ";
		sql+="		and v2.FNumber like 'Y.%'                                                                                                                                             ";
		sql+="		and v1.FDate >= @RQDAY1_L1  and MONTH(v1.FDate) <  @RQDAY1_L2+1                                                                                                       ";
		sql+="		group by v2.FName                                                                                                                                                     ";
		sql+="		) V2 ON V1.FName = V2.FName                                                                                                                                           ";
		sql+="		                                                                                                                                                                      ";
		sql+="		LEFT JOIN                                                                                                                                                             ";
		sql+="		                                                                                                                                                                      ";
		sql+="		(                                                                                                                                                                     ";
		//sql+="		 -- 去年平均单价                                                                                                                                                      ";
		sql+="		select v2.FName,avg(isnull(u1.Fauxprice,0)) FPrice3                                                                                                                   ";
		sql+="		from RemoteServer.AIS2009.dbo.ICStockBill v1 INNER JOIN RemoteServer.AIS2009.dbo.ICStockBillEntry u1 ON     v1.FInterID = u1.FInterID   AND u1.FInterID <>0           ";
		sql+="		LEFT JOIN RemoteServer.AIS2009.dbo.t_ICItem  v2 ON u1.FItemID=v2.FItemID                                                                                              ";
		sql+="		where 1=1 AND  (v1.FTranType=1 AND ((v1.FCheckerID>0)  and  v1.FCancellation = 0))                                                                                    ";
		sql+="		and v2.FNumber like 'Y.%'                                                                                                                                             ";
		sql+="		and YEAR(v1.FDate) = @YEAR2  and MONTH(v1.FDate) = @MONTH2                                                                                                            ";
		sql+="		group by v2.FName                                                                                                                                                     ";
		sql+="		) V3 ON V1.FName = V3.FName                                                                                                                                           ";
		sql+="	) T	                                                                                                                                                                      ";


		List<Map> list = saleMiniDao.execContract5(sql);
		dataGrid.setTotal(list.size());
		dataGrid.setResults(list);
		
/*		   // 数量合计
				BigDecimal sum_qty = new BigDecimal("0.0000");
				BigDecimal sum_qty2 = new BigDecimal("0.0000");
				BigDecimal sum_qty3 = new BigDecimal("0.0000");
				BigDecimal sum_qty4 = new BigDecimal("0.0000");
				
				for (Map<String, Object> map : list)
				{
				   sum_qty = sum_qty.add((BigDecimal) map.get("Qty1"));
				   sum_qty2 = sum_qty2.add((BigDecimal) map.get("Qty2"));
				   sum_qty3 = sum_qty3.add((BigDecimal) map.get("Qty3"));
				   sum_qty4 = sum_qty4.add((BigDecimal) map.get("Qty4"));
				}
				
				dataGrid.setFooter("FName:合计" + ",Qty1:" + sum_qty+ ",Qty2:" + sum_qty2+ ",Qty3:" + sum_qty3+ ",Qty4:" + sum_qty4);*/
		TagUtil.datagrid(response, dataGrid);   
	}
	
	
	
	/**
	 * easyui AJAX采购合同执行情况   
	 * 
	 * @param request
	 * @param response
	 * @param dataGrid
	 * @param user
	 */

	@RequestMapping(params = "datagrid7")
	public void datagrid7(HttpServletRequest request, HttpServletResponse response, DataGrid dataGrid) {
		
		String FDate_begin = request.getParameter("FDate_begin");
		String FDate_end = request.getParameter("FDate_end");
		
		String FCustomerName = request.getParameter("FCustomerName");
		String FItemName = request.getParameter("FItemName");
		String FContractNo = request.getParameter("FContractNo");
		
		
		//sql+="	--  采购合同执行情况                                                                                                                                                          ";
		String sql="	DECLARE @RQ1 SMALLDATETIME                                                                                                                                                    ";
		sql+="	DECLARE @RQ2 SMALLDATETIME                                                                                                                                                    ";
		sql+="                                                                                                                                                                                ";
		sql+="	DECLARE @RQDAY1_M SMALLDATETIME                                                                                                                                               ";
		sql+="	DECLARE @RQDAY1_L2 SMALLDATETIME                                                                                                                                              ";
		sql+="                                                                                                                                                                                ";
		//sql+="	--日期参数                                                                                                                                                                    ";
		//sql+="	SET @RQ1='2015-01-01'                                                                                                                                                         ";
		//sql+="	SET @RQ2='2016-01-19'                                                                                                                                                         ";
		sql+="	SET @RQ1='" + FDate_begin + "'"; 
		sql+="	SET @RQ2='" + FDate_end + "'"; 
		
		sql+="                                                                                                                                                                                ";
		sql+="                                                                                                                                                                                ";
		//sql+="	--本月第一天                                                                                                                                                                  ";
		sql+="	SELECT @RQDAY1_M=  CONVERT(CHAR(10),DATEADD(dd,-DAY(@RQ2)+1,@RQ2),111)                                                                                                        ";
		sql+="                                                                                                                                                                                ";
		//sql+="	--上月最后一天                                                                                                                                                                ";
		sql+="	SELECT @RQDAY1_L2=CONVERT(CHAR(10),DATEADD(ms,-3,DATEADD(mm, DATEDIFF(mm,0,@RQ2),0)),111)+' 00:00:00'                                                                         ";
		sql+="                                                                                                                                                                                ";
		//sql+="	--购货单位 合同日期 合同号  规格 硬度 含税单价 合同签订量  至上月底已发量  上月欠交量 本月签订量  本月欠交量                                                                  ";
		sql+="                                                                                                                                                                                ";
		sql+="                                                                                                                                                                                ";
		//sql+="	--查询基础开始时间未执行完的合同                                                                                                                                              ";
		sql+="	select V1.*,isnull(V2.BYQDL,0) BYQDL,Convert(decimal(18,4),isnull(V3.Qty,0)) SYRKL,case when (isnull(V4.FQty1,0) - isnull(V4.FQty2,0)) < 0 then 0 else (isnull(V4.FQty1,0) - isnull(V4.FQty2,0))  end as SYQJL,";
		sql+="	case when (isnull(V1.FQty1,0) -isnull(V1.FQty2,0)) < 0 then 0 else (isnull(V1.FQty1,0) -isnull(V1.FQty2,0)) end as BYQJL from (                                               ";
		sql+="	select T1.*,T2.FQty2,t_Item1.FName FCustomerName,t_ICItem.FName FItemName,t_ICItem.FModel from (                                                                              ";
		sql+="	  select t1.FCustomer,t1.FContractID,t1.FContractNo,t1.FDate,t2.FProductID,SUM(t2.FQuantity) FQty1,T2.FTaxPrice                                                               ";
		sql+="	  from RemoteServer.AIS2009.dbo.t_rpContract t1 Inner Join RemoteServer.AIS2009.dbo.t_rpContractEntry t2 on t1.FContractID=t2.FContractID                                     ";
		sql+="	 where t1.Fstatus<2 AND isnull(t1.FChecker,0)>0 and t1.FClassTypeID = 1000020                                                                                                 ";
		sql+="	 and t1.FDate >= @RQ1   and t1.FDate < @RQ2+1                                                                                                                                 ";
		sql+="	 group by t1.FCustomer,t1.FContractNo,t1.FDate,t1.FContractID,t2.FProductID,T2.FTaxPrice                                                                                      ";
		sql+="				  )T1 LEFT JOIN (                                                                                                                                                 ";
		sql+="	 select v1.FSupplyID,u1.FItemID,SUM(u1.FAuxQty) FQty2                                                                                                                         ";
		sql+="	 from RemoteServer.AIS2009.dbo.ICStockBill v1 INNER JOIN RemoteServer.AIS2009.dbo.ICStockBillEntry u1 ON     v1.FInterID = u1.FInterID   AND u1.FInterID <>0                  ";
		sql+="	 where 1=1 AND  (v1.FTranType=1 AND ((v1.FCheckerID>0)  and  v1.FCancellation = 0))                                                                                           ";
		sql+="	 and v1.FDate >= @RQ1   and v1.FDate < @RQ2+1                                                                                                                                 ";
		sql+="	 group by v1.FSupplyID,u1.FItemID                                                                                                                                             ";
		sql+="							   )T2 ON T1.FCustomer = T2.FSupplyID and T1.FProductID = T2.FItemID                                                                                  ";
		sql+="							   LEFT JOIN RemoteServer.AIS2009.dbo.t_ICItem  t_ICItem ON T1.FProductID=t_ICItem.FItemID                                                            ";
		sql+="							   LEFT  JOIN RemoteServer.AIS2009.dbo.t_ITem t_Item1 ON T1.FCustomer=t_Item1.FItemID AND t_Item1.FItemID<>0                                          ";
		sql+="			   ) V1                                                                                                                                                               ";
		sql+="								                                                                                                                                                  ";
		sql+="                                                                                                                                                                                ";
		sql+="	LEFT JOIN                                                                                                                                                                     ";
		sql+="	 (                                                                                                                                                                            ";
		//sql+="	-- 本月签订量                                                                                                                                                                 ";
		sql+="	select t1.FCustomer,t1.FContractID,t1.FContractNo,t1.FDate,t2.FProductID,SUM(t2.FQuantity) BYQDL                                                                              ";
		sql+="	from RemoteServer.AIS2009.dbo.t_rpContract t1 Inner Join RemoteServer.AIS2009.dbo.t_rpContractEntry t2 on t1.FContractID=t2.FContractID                                       ";
		sql+="	where t1.Fstatus<2 AND isnull(t1.FChecker,0)>0 and t1.FClassTypeID = 1000020                                                                                                  ";
		sql+="	and t1.FDate >= @RQDAY1_M   and t1.FDate <= @RQ2                                                                                                                              ";
		sql+="	group by t1.FCustomer,t1.FContractNo,t1.FDate,t1.FContractID,t2.FProductID                                                                                                    ";
		sql+="	) V2 ON V1.FCustomer = V2.FCustomer AND V1.FContractID = V2.FContractID AND V1.FProductID = V2.FProductID                                                                     ";
		sql+="                                                                                                                                                                                ";
		sql+="	LEFT JOIN                                                                                                                                                                     ";
		sql+="	(                                                                                                                                                                             ";
		//sql+="	--至上月底外购入库量                                                                                                                                                          ";
		sql+="	select v1.FSupplyID,u1.FItemID,SUM(u1.FAuxQty) Qty                                                                                                                            ";
		sql+="	from RemoteServer.AIS2009.dbo.ICStockBill v1 INNER JOIN RemoteServer.AIS2009.dbo.ICStockBillEntry u1 ON     v1.FInterID = u1.FInterID   AND u1.FInterID <>0                   ";
		sql+="	where 1=1 AND  (v1.FTranType=1 AND ((v1.FCheckerID>0)  and  v1.FCancellation = 0))                                                                                            ";
		sql+="	and v1.FDate < @RQDAY1_L2+1                                                                                                                                                   ";
		sql+="	group by v1.FSupplyID,u1.FItemID                                                                                                                                              ";
		sql+="	) V3 ON V1.FCustomer = V3.FSupplyID AND V1.FProductID = V3.FItemID                                                                                                            ";
		sql+="                                                                                                                                                                                ";
		sql+="	LEFT JOIN                                                                                                                                                                     ";
		sql+="                                                                                                                                                                                ";
		sql+="	(                                                                                                                                                                             ";
		sql+="	select t1.*,t2.FQty2 from (                                                                                                                                                   ";
		sql+="	select t1.FCustomer,t1.FContractID,t1.FContractNo,t1.FDate,t2.FProductID,SUM(t2.FQuantity) FQty1                                                                              ";
		sql+="	  from RemoteServer.AIS2009.dbo.t_rpContract t1 Inner Join RemoteServer.AIS2009.dbo.t_rpContractEntry t2 on t1.FContractID=t2.FContractID                                     ";
		sql+="	 where t1.Fstatus<2 AND isnull(t1.FChecker,0)>0 and t1.FClassTypeID = 1000020                                                                                                 ";
		sql+="	 and t1.FDate >= @RQ1   and t1.FDate < @RQDAY1_L2                                                                                                                             ";
		sql+="	 group by t1.FCustomer,t1.FContractNo,t1.FDate,t1.FContractID,t2.FProductID                                                                                                   ";
		sql+="				  )T1 LEFT JOIN (                                                                                                                                                 ";
		sql+="	 select v1.FSupplyID,u1.FItemID,SUM(u1.FAuxQty) FQty2                                                                                                                         ";
		sql+="	 from RemoteServer.AIS2009.dbo.ICStockBill v1 INNER JOIN RemoteServer.AIS2009.dbo.ICStockBillEntry u1 ON     v1.FInterID = u1.FInterID   AND u1.FInterID <>0                  ";
		sql+="	 where 1=1 AND  (v1.FTranType=1 AND ((v1.FCheckerID>0)  and  v1.FCancellation = 0))                                                                                           ";
		sql+="	 and v1.FDate >= @RQ1   and v1.FDate < @RQDAY1_L2                                                                                                                             ";
		sql+="	 group by v1.FSupplyID,u1.FItemID                                                                                                                                             ";
		sql+="							   )T2 ON T1.FCustomer = T2.FSupplyID and T1.FProductID = T2.FItemID                                                                                  ";
		sql+="	) V4 ON V1.FCustomer = V4.FCustomer AND V1.FContractID = V4.FContractID AND V1.FProductID = V4.FProductID                                                                     ";
       
		
		if (StringUtil.isNotEmpty(FCustomerName)) {
			sql += " and V1.FCustomerName like " + "'%" + FCustomerName + "%'";
		} 
		if (StringUtil.isNotEmpty(FItemName)) {
			sql += " and V1.FItemName like " + "'%" + FItemName + "%'";
		} 
		if (StringUtil.isNotEmpty(FContractNo)) {
			sql += " and V1.FContractNo like " + "'%" + FContractNo + "%'";
		} 
		
		List<Map> list = saleMiniDao.execContract6(sql);
		dataGrid.setTotal(list.size());
		dataGrid.setResults(list);
		
/*		   // 数量合计
				BigDecimal sum_qty = new BigDecimal("0.0000");
				BigDecimal sum_qty2 = new BigDecimal("0.0000");
				BigDecimal sum_qty3 = new BigDecimal("0.0000");
				BigDecimal sum_qty4 = new BigDecimal("0.0000");
				
				for (Map<String, Object> map : list)
				{
				   sum_qty = sum_qty.add((BigDecimal) map.get("Qty1"));
				   sum_qty2 = sum_qty2.add((BigDecimal) map.get("Qty2"));
				   sum_qty3 = sum_qty3.add((BigDecimal) map.get("Qty3"));
				   sum_qty4 = sum_qty4.add((BigDecimal) map.get("Qty4"));
				}
				
				dataGrid.setFooter("FName:合计" + ",Qty1:" + sum_qty+ ",Qty2:" + sum_qty2+ ",Qty3:" + sum_qty3+ ",Qty4:" + sum_qty4);*/
		TagUtil.datagrid(response, dataGrid);   
	}
}
